CODEWITHSUNDEEP
c#.netconcurrencylockingsqlcommand
noahsrc
noahsrc

Reputation: 13

Using Updlock and Readpast in C#

I am looking to concurrently read and update records from a database. At this point, I have a sample program set up that grabs records from a database with a certain status.

I am aiming for the rows to lock when the select command executes. This code block executes without any errors, however the rows do not lock during the lifespan of the transaction. Are these hints supported or is there a way that this can be done?

This is the Visual Studio code. I will be adding update commands at a later time.

SqlDataReader data;
DataTable table = new DataTable();    
string query = string.Format(@"select * from [{0}] with (updlock,readpast) where [status] = '{1}'","table","abc");

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();

    SqlTransaction trans = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

    SqlCommand cmd = new SqlCommand(query, conn, trans);
    data = cmd.ExecuteReader();
    table.Load(data);

    System.Threading.Thread.Sleep(15000); // to test locking
}

This is the original transaction I tested with in SQL Server. This works just fine.

BEGIN TRAN TEST
   select * from table with (updlock, readpast) where status = 'abc';
   WAITFOR DELAY '00:00:15' 
COMMIT

Upvotes: 1

Views: 904

Answers (1)

noahsrc
noahsrc

Reputation: 13

When testing this, I was using SQL server and running a select statement to check whether I could read the locked rows. I neglected to add UPDLOCK and READPAST hints to the select query. This code works how it is supposed to.

I ended up running the C# transaction with a wait time of 30 seconds before completion. During these 30 seconds, I ran these commands in SQL Server to test that the rows locked;

select * from table with (updlock, readpast) where status = 'abc';
update table with (readpast) set status = 'cba' where status = 'abc';

The rows do lock and cannot be updated. They can, however, still be read using a select statement without hints. This should be fine for the system I am upgrading.

Upvotes: 0

Related Questions