Lock MSSQL Table Row?

[xenophon]

In my .NET Framework 1.1 DataSet/DataTable/DataRow, I have a row with
a stringized GUID column called 'Concurrent'. There is a matching row
in the database with the same GUID. Within a single XA (MSDTC)
transaction (not SQL Server transaction), I need to "lock" the
existing row if the 'Concurrent' values match, update the database
table(s), change the 'Concurrent' value in the database and in the
DataRow, and then unlock at the database. That way, if two people
tried to save the same data, the second user would be blocked until
the first save was complete, and then could take action because
'Concurrent' would no longer match.

How can I lock a database row from an MSDTC transaction in C#? Is
there any good sample code for other means of concurrency management?

Thanks.

 

[Steven Cheng[MSFT]]

Hi Xenophon,

Thank you for your posting. Regarding on the issue, I am
finding proper resource to assist you and we will update as soon as posible.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security(This posting is provided "AS IS",
with no warranties, and confers no rights.)
--------------------
| NNTP-Posting-Date: Fri, 30 Sep 2005 13:26:11 -0500
| From: xenophon <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Subject: Lock MSSQL Table Row?
| Date: Fri, 30 Sep 2005 14:16:49 -0400
| Message-ID: <[email protected]>
| X-Newsreader: Forte Agent 2.0/32.646
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Lines: 22
| X-Trace:
sv3-6Dfc2ay3nzyDYhSlT7hEqRn0VnAO3j9E8binO7HQANOSEZA9UktrVnZmbrCPF4iOMbUIWmmG
jGmlB0a!x8AveHqnQToYst0/eH+cO9VD0TY90EDU9VsGD2WpmwXYTRy/YOCayZlkPt86GHtsvVLr
AlY=
| X-Complaints-To: (e-mail address removed)
| X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
| X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
| X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your
complaint properly
| X-Postfilter: 1.3.32
| Path:
TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.gigan
ews.com!local01.nntp.dca.giganews.com!news.giganews.com.POSTED!not-for-mail
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.dotnet.framework.adonet:36640
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
|
|
|
| In my .NET Framework 1.1 DataSet/DataTable/DataRow, I have a row with
| a stringized GUID column called 'Concurrent'. There is a matching row
| in the database with the same GUID. Within a single XA (MSDTC)
| transaction (not SQL Server transaction), I need to "lock" the
| existing row if the 'Concurrent' values match, update the database
| table(s), change the 'Concurrent' value in the database and in the
| DataRow, and then unlock at the database. That way, if two people
| tried to save the same data, the second user would be blocked until
| the first save was complete, and then could take action because
| 'Concurrent' would no longer match.
|
| How can I lock a database row from an MSDTC transaction in C#? Is
| there any good sample code for other means of concurrency management?
|
| Thanks.
|
|
|
|
|

 

[Kevin Yu [MSFT]]

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."

 

[xenophon]

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.

 

[Kevin Yu [MSFT]]

Hi xenophon,

I think there is something wrong with the document. The DataSet should mean
a set of data in the database, not the in-memory Dataset object. I think
your steps are fine.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[xenophon]

So even though I am using the OleDb provider instead of the Managed
SQL provider, my steps are fine? Does that mean I can use the OleDb
provider for other stores like Access and Visual FoxPro and PostgreSql
and have the same lock safety?

Thanks.

 

[Kevin Yu [MSFT]]

Hi xenohpon,

It is fine with the OleDbProvider. However, if you need to apply this to
other databases, the database must have transaction support. Or this will
not work, exceptions might be thrown.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."