September 18, 2002 at 1:20 am
Hi all,
We are facing a problem regarding locking of rows in a table, which leads to a
deadlock situation.If a SELECT statement is issued for a particular table, a lock is
acquired by SQL server(found out this in the TRACE utility) and released subsequently.
But that is not rational( Three locks are acquired but only two are released).
We would be glad if some body throw light on how SQL Server handles locking.
Also we would like to know if there is any way to avoid locking of rows when a table is queried
(SQL Select).
Thanking you in anticipation
Pro
September 18, 2002 at 1:44 am
If your SELECT statement is non-critical, for example a web page that refreshes regularly so bad data won't matter, use the WITH (NOLOCK) hint after your table object name:
SELECT name FROM db.dbo.foo WITH (NOLOCK) WHERE state='CA'
This tells SQL to ignore locks placed on the table or rows, but you might get dirty data which is about to be updated, hence my first comment. If the SELECT is critical for accuracy, then there is little you can do, except make sure it is as specific as possible, i.e. consider a covering index.
Simon
September 18, 2002 at 1:46 am
R u using transactions?
It happens because of transactions sometimes, You need to commit such transactions to avoid locks.
if you don't mind selecting non committed data then you can use
select * from table with (nolock)
clause to avoid locks.
One more thing, sequence of accessing tables in the transactions should be same.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply