>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[i]["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 [TABLE] set RefundStatus = 3 where CarrierCode = 'RMG'
>update [TABLE] set RefundStatus = 3 where CarrierCode = 'USPS'
>update [TABLE] 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.