Re: Lock MSSQL Table Row?


I am using OleDb to connect to MSSQL. I am also using it to connect to
PostgreSql and Access 2003 and Visual FoxPro 9.0.

In my data store (let's just use MSSQL 2000 in this example), I have a
parent table with a "lock" column containing a GUID. The application
reads the parent table and 2 child tables into a DataSet. Data is
modified in all DataTables in the DataSet.

When a save happens, I want to lock the parent row, compare the "lock"
column in the data store to the "lock" column of the DataSet, and if
there is a match make changes in the parent and all children, change
the "lock" column in the dataset to reflect the change, and then
issue an "unlock". If another user/thread attempts to do the same
save, they are blocked until the lock on the parent table is released,
and then their save will fail because the "lock"column does not match
the DataSet "lock" column.

So....

Are you saying that I can do this:

1. Open OleDbConnection to my data store.
2. Create OleDbCommand and set connection.
3. Set OleDbCommand text to "select * from parenttable where...."
4. Do OleDbTransaction lockTrans = connection.BeginTransaction();
5. Do lockTrans.IsolationLevel = IsolationLevel.Serializable;
6. Do OleDbCommand.ExecuteScalar(), which will lock the row of the
parent table.
7. Use the existing OleDbConnection with new OleDbCommands to do
updating from the DataSet/DataTables->database.
8. Use the old OleDbCommand to "update parenttable where...." to set
the "lock" column GUID to something new.
9. Close the connection, which will unblock the database parent row
from others trying to update?

Please let me know, this is very interesting.


The VS.NET 2003 help says:
" A range lock is placed on the System.Data.DataSet, preventing other
users from updating or inserting rows into the dataset until the
transaction is complete. "

I'm not using a DataSet as far as locking is concerned, am I?




Thanks.


On Tue, 04 Oct 2005 08:36:47 GMT, v-kevy@xxxxxxxxxxxxxxxxxxxx (Kevin
Yu [MSFT]) wrote:

>Hi xenophon,
>
>First of all, I would like to confirm my understanding of your issue. From
>your description, I understand that you need to lock a certain row that
>one user is modifying on it. If there is any misunderstanding, please feel
>free to let me know.
>
>I suggest you try to use SqlConnection.BeginTransaction to start a
>transaction, and set its IsolationLevel to Serializable. It will place a
>range lock on the resultset, preventing other users from updating or
>inserting rows into the dataset until the transaction is complete. Here is
>a link for your reference.
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
>frlrfsystemdatasqlclientsqltransactionclassisolationleveltopic.asp
>
>Kevin Yu
>=======
>"This posting is provided "AS IS" with no warranties, and confers no
>rights."
>

.

Relevant Pages

  • Re: lock used in thread and by event
    ... First class has a method which use lockand then it put itself ... public Parent() ... lock ... public void CalledByEvent() ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: device struct bloat
    ... Just locking the tree root is not enough? ... modifying operation to descend into the tree). ... I'd first start by asking if you want to lock all the children or the ... parent again. ...
    (Linux-Kernel)
  • Re: Race condition in debugger?
    ... What happens is that the debugger hangs at some ... that there's no lock protecting that the conditions in the ... >hold the parent lock across the bulk of kern_wait, ... the race has just occurred. ...
    (freebsd-current)
  • Re: device struct bloat
    ... acquires the semaphores for _all_ the devices in the tree. ... any time a driver's probe routine tries to register a child ... lock in order to probe drivers for the child. ... (it having a locked child, your parent, should be ...
    (Linux-Kernel)
  • Re: device struct bloat
    ... acquires the semaphores for _all_ the devices in the tree. ... lock in order to probe drivers for the child. ... parent again. ...
    (Linux-Kernel)