Implement ADO.NET Row Level Pessimistic Locking

M

MSDeveloper

I am trying to implement record level pessimistic locking using ADO.NET
and SQL Server 2000. A reduced structure of the table is shown:

CarrierName(varchar) CarrierCode(varchar) RefundStatus(int)
------------------------------------------------------------------------
--------
US Postal Service USPS 1
US Postal Service USPS 2
Canada Postal Service CPC 1
UK Postal Service RMG 1
UK Postal Service RMG 2

I have a win form application(C#), which on a button click selects only
the RMG records and then updates these records. The requirement is that,
during this operation these RMG records must be pessimistically locked
so that no other application can update these records during this time,
but the USPS and CPC records should remain updateable.

After browsing through different forums, I realised that ADO.NET does
not support pessimistic concurrency directly but there is a workaround
using transaction. This is how I implemented the solution:

private const string DBConnectionString = "Database=BnBCSO;
Server=10.20.1.19;UID=sa;PWD=rebaca;" + "Connection
Timeout=15;Enlist=False;Max Pool Size=150; Min Pool Size=5;";
private IsolationLevel oIsolationLevel = IsolationLevel.Serializable;
private string SelectStatement = "select * from PackageRefund where
CarrierCode = 'RMG'";

private void Process()
{
SqlConnection oConn = null;
SqlTransaction oTrxn = null;
SqlDataAdapter oDataAdapter = null;
DataSet ds = new DataSet();

try
{
oConn = new SqlConnection( DBConnectionString );
oConn.Open();
oTrxn = oConn.BeginTransaction( oIsolationLevel );
SqlCommand oCommand = new SqlCommand(
SelectStatement, oConn, oTrxn );
oDataAdapter = new SqlDataAdapter( oCommand );
oDataAdapter.Fill( ds );

for( int i = 0; i < ds.Tables[0].Rows.Count; i++ )
{
Thread.Sleep( 5000 );
ds.Tables[0].Rows["RefundStatus"] = 4;
}

SqlCommandBuilder oCommandBuilder = new
SqlCommandBuilder( oDataAdapter );
oDataAdapter.Update( ds );
oTrxn.Commit();
}
catch( Exception exc )
{
oTrxn.Rollback();
}
finally
{
if( oDataAdapter != null )
{
oDataAdapter.Dispose();
oDataAdapter = null;
}
if( oTrxn != null )
{
oTrxn.Dispose();
oTrxn = null;
}
if( oConn != null )
{
oConn.Close();
oConn.Dispose();
oConn = null;
}
}
}

private void cmdStart_Click(object sender, System.EventArgs e)
{
try
{
Thread oThread = new Thread( new ThreadStart(
Process ) );

oThread.Start();
}
catch( Exception exc )
{
}
}

The issue is that after I have started the program, I go to SQL Query
Analyzer and execute these sql statements separately:

update
set RefundStatus = 3 where CarrierCode = 'RMG'
update
set RefundStatus = 3 where CarrierCode = 'USPS'
update
set RefundStatus = 3 where CarrierCode = 'CPC'

If record level pessimistic locking was properly implemented, then the
USPS and CPC updates should have succeded while the RMG one should
wait/timeout. But in this case, all the three update statements are
waiting and eventually timing out, possibly waiting for the lock to be
released. That means that this solution has implemented a table lock
rather than a row lock.

What do I have to change if I want only the selected records to be
locked while the other records in the same table should be updateable.

Thanks.
 
M

Mary Chipman [MSFT]

If you set the isolation level = serializable for a connection, then
it stays in effect until the connection is closed or it is reset to
another isolation level.

Using the default isolation level of read committed ensures that only
records being modified will be affected--SQLS automatically escalates
locks those rows while leaving other rows updateable. If I understand
your code correctly, you are locking all rows at the outset by using
serializable. Both the modified rows and rows not being updated will
be locked until the transaction either commits or is rolled back,
which is what serializable means--that you can re-run a transaction
and it will have the same effect. Simply selecting rows without
updating anything inside of a serializable explicit transaction will
lock those rows until either a commit or a rollback is issued. No
other processes can even read them.

If you search on "SQL Server isolation levels" you will come up with
some good hits that explain what's going on in more detail.

--Mary

I am trying to implement record level pessimistic locking using ADO.NET
and SQL Server 2000. A reduced structure of the table is shown:

CarrierName(varchar) CarrierCode(varchar) RefundStatus(int)
------------------------------------------------------------------------
--------
US Postal Service USPS 1
US Postal Service USPS 2
Canada Postal Service CPC 1
UK Postal Service RMG 1
UK Postal Service RMG 2

I have a win form application(C#), which on a button click selects only
the RMG records and then updates these records. The requirement is that,
during this operation these RMG records must be pessimistically locked
so that no other application can update these records during this time,
but the USPS and CPC records should remain updateable.

After browsing through different forums, I realised that ADO.NET does
not support pessimistic concurrency directly but there is a workaround
using transaction. This is how I implemented the solution:

private const string DBConnectionString = "Database=BnBCSO;
Server=10.20.1.19;UID=sa;PWD=rebaca;" + "Connection
Timeout=15;Enlist=False;Max Pool Size=150; Min Pool Size=5;";
private IsolationLevel oIsolationLevel = IsolationLevel.Serializable;
private string SelectStatement = "select * from PackageRefund where
CarrierCode = 'RMG'";

private void Process()
{
SqlConnection oConn = null;
SqlTransaction oTrxn = null;
SqlDataAdapter oDataAdapter = null;
DataSet ds = new DataSet();

try
{
oConn = new SqlConnection( DBConnectionString );
oConn.Open();
oTrxn = oConn.BeginTransaction( oIsolationLevel );
SqlCommand oCommand = new SqlCommand(
SelectStatement, oConn, oTrxn );
oDataAdapter = new SqlDataAdapter( oCommand );
oDataAdapter.Fill( ds );

for( int i = 0; i < ds.Tables[0].Rows.Count; i++ )
{
Thread.Sleep( 5000 );
ds.Tables[0].Rows["RefundStatus"] = 4;
}

SqlCommandBuilder oCommandBuilder = new
SqlCommandBuilder( oDataAdapter );
oDataAdapter.Update( ds );
oTrxn.Commit();
}
catch( Exception exc )
{
oTrxn.Rollback();
}
finally
{
if( oDataAdapter != null )
{
oDataAdapter.Dispose();
oDataAdapter = null;
}
if( oTrxn != null )
{
oTrxn.Dispose();
oTrxn = null;
}
if( oConn != null )
{
oConn.Close();
oConn.Dispose();
oConn = null;
}
}
}

private void cmdStart_Click(object sender, System.EventArgs e)
{
try
{
Thread oThread = new Thread( new ThreadStart(
Process ) );

oThread.Start();
}
catch( Exception exc )
{
}
}

The issue is that after I have started the program, I go to SQL Query
Analyzer and execute these sql statements separately:

update
set RefundStatus = 3 where CarrierCode = 'RMG'
update
set RefundStatus = 3 where CarrierCode = 'USPS'
update
set RefundStatus = 3 where CarrierCode = 'CPC'

If record level pessimistic locking was properly implemented, then the
USPS and CPC updates should have succeded while the RMG one should
wait/timeout. But in this case, all the three update statements are
waiting and eventually timing out, possibly waiting for the lock to be
released. That means that this solution has implemented a table lock
rather than a row lock.

What do I have to change if I want only the selected records to be
locked while the other records in the same table should be updateable.

Thanks.
Click to expand...
 
Top