VB and VBA Users Source Code: Hint: Effective Row level locking in SQL Server
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Hint: Effective Row level locking in SQL Server
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Wednesday, July 28, 2004
Hits:
1333
Category:
Database/SQL/ADO
Article:
If the where clause in your update statement results in a table scan (i.e. you don't have a unique index on the fields in the where clause), then SQL server will apply a SHARED or UPDATE lock to rows as they are scanned (SHARED for selects, and UPDATE for update statements). If another connection is performing an update in a transaction then it will apply an exclusive row level lock to the row/s it's updating. While this exclusive row level lock exists the table scan will not be able to apply a shared lock s and hence you will get undesirable locking or deadlocking... To get round this make sure you use the appropriate unique index/s. Example: -- STEP 1 -- -- Create a new table -- create table TestTable (col1 int not null) -- NOTE, IF YOU DO NOT APPLY THIS INDEX YOU WILL -- NOT BE ABLE PEFORM THE UPDATE IN STEP 4 -- -- STEP 2 -- -- Add a unique index alter table TestTable add primary key (col1) -- STEP 2 -- -- Add data to table insert into TestTable values (1) insert into TestTable values (2) -- STEP 3 -- -- Open a new connection and run the following sql -- begin transaction update TestTable with (rowlock) set col1 = 1 where col1 = 1 -- DON'T RUN THIS NEXT LINE OF SQL UNTIL YOU HAVE COMPLETED STEP 4 -- commit transaction -- STEP 4 -- -- Open another new connection and run the following sql -- update TestTable with (rowlock) set col1 = 2 where col1 = 2 -- THIS WILL ONLY BLOCK IF YOU HAVE NOT APPLIED INDEX IN STEP 2 --
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
