VB and VBA Users Source Code: Resolving read locks on SQL server tables
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Resolving read locks on SQL server tables
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Sunday, August 11, 2002
Hits:
1249
Category:
Database/SQL/ADO
Article:
If you have a table which is polled at regular intervals by several users and is also frequently updated, you may notice the upload speeds are affected by the frequency at which the table is polled. This is due to the way in which SQL server puts locks on the tables during reading operations (refered to as the isolation level). SQL Server's default table locking uses an isolation level of READ COMMITTED, in which SQL Server holds shared locks only until the data has been read, and holds exclusive locks until the end of the transaction. A transaction operating in READ UNCOMMITTED isolation level takes no locks and ignores locks other transactions hold. A transaction operating in REPEATABLE READ isolation level keeps shared locks and exclusive locks until the end of the transaction. The most restrictive isolation level, SERIALIZABLE, locks data ranges the transaction specifies and holds all locks until the end of the transaction, so that users can't insert new rows into those ranges. Listed below are examples of setting isolation levels for reads: select * from tblpublishers where author like 'Andrew%' ORDER BY author at isolation read uncommitted OR set transaction isolation level READ UNCOMMITTED --Notes: --1. Values are READ COMMITTED, READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE --2. Only one of the options can be set at a time, and it remains set for that --connection until it is explicitly changed. This becomes the default behavior --unless an optimization option is specified at the table level in the FROM --clause of the statement BEGIN TRANSACTION select * from tblpublishers where author like 'Andrew%' ORDER BY author COMMIT TRANSACTION
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
