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