13

Very much a newbie on DB work, so appreciate your patience with a basic question. I'm running SQL Server 2014 on my local machine, and I have a small table and a basic client application to test different approaches with. I'm getting what appears to be a table lock during both INSERT INTO and UPDATE statements. The client is an ASP.NET application with the following code:

OleDbConnection cn = new OleDbConnection("Provider=SQLNCLI11; server=localhost\\SQLEXPRESS; Database=<my db>; user id=<my uid>; password=<my pwd>");
cn.Open();
OleDbTransaction tn = cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("INSERT INTO LAYOUTSv2 (LAYOUTS_name_t, LAYOUTS_enabled_b, LAYOUTS_data_m) VALUES ('name', '-1', 'data')", cn, tn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
int newkey = Decimal.ToInt32((decimal)cmd.ExecuteScalar());
Console.WriteLine("Created index " + newkey);
Thread.Sleep(15000);
tn.Commit();
tn = cn.BeginTransaction();
cmd.CommandText = "UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key='" + newkey + "'";
cmd.Transaction = tn;
cmd.ExecuteNonQuery();
Console.WriteLine("updated row");
Thread.Sleep(15000);
tn.Rollback();
cn.Close();

I run this code, then from the management studio I run SELECT * FROM LAYOUTSv2. During both cases when the client thread is paused (i.e. prior to commit/rollback) the SELECT query hangs until the commit/rollback occurs.

The table has field LAYOUTS_key assigned as the primary key. In the properties window it shows that it is unique and clustered, with page locks and row locks both allowed. The lock escalation setting for the table is Disable...I've tried both the other available settings of Table and AUTO with no changes. I've tried SELECT ... WITH (NOLOCK) and that returns a result immediately, but as is well-cautioned here and other places it's not what I should be doing. I've tried putting the ROWLOCK hint on both the INSERT and UPDATE statements, but nothing has changed.

The behavior I'm looking for is this: prior to commit of an INSERT, queries from other threads read all rows except the one that's being INSERTed. Prior to commit of an UPDATE queries from other threads read the starting version of the row being UPDATEed. Is there any way I can do this? If I need to provide other information to clarify my use case please let me know. Thanks.

  • 3
    By the way WHERE LAYOUTS_key='" + newkey + "' is a complete no-no for various reasons including SQL injection, you should use parameterised queries. – Martin Smith Jan 24 '16 at 20:12
  • 1
    @MartinSmith Thanks for the heads-up on this...never heard of either parameterized queries or SQL Injection attacks. – John Riehl Jan 24 '16 at 21:12
  • @JohnRiehl, re: injection attacks, imagine if your user sets newkey to "something';DELETE FROM LAYOUTSv2 --". Your update would complete successfully, and then empty the table because the user manipulated the query by inserting an apostrophe. Normally, a parameterized query looks something like UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key=?, after which you separately assign value(s) to the ? (the parameter) in your code. – Daniel Hutmacher Jan 25 '16 at 13:05
9

Chances are it isn't locking the "whole table".

It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock.

For the insert case you can merely specify the READPAST hint to skip past the locked row- however that will not give your desired result for the UPDATE case (it will skip the row again not read the starting version of the row).

If you configure the database for read committed snapshot isolation this will give your desired effect for both cases (at the expense of greater use of tempdb)

  • I changed "Is Read Committed Snapshot On" to True and now it works perfectly with no hints needed. Thanks! One follow-up...I left "Allow Snapshot Isolation" set to False...is that OK? Thanks. – John Riehl Jan 24 '16 at 19:56
  • @JohnRiehl - Yes if your aren't explicitly using SNAPSHOT isolation best to leave it disabled and then enable it if you subsequently decide this would be useful for you. – Martin Smith Jan 24 '16 at 19:59
6

The insert and update statements are supposed to create row-level locks. However, when the number of locks in any transaction is 5,000 or more then a lock escalation occurs and it creates a table level lock. Please see below.

https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.