We help IT Professionals succeed at work.

I like to want lock will held for the specific query

316 Views
Last Modified: 2013-01-22
I want to know what lock is been put on the table if run below statement

Select top 5000 from table1

I tried to examine by sp_lock spid;

I gives type as TAB.

And if run the Select top 1 from table1 it also say the type as TAB.
In the MSDN TAB = Lock on an entire table, including all data and indexes.

I want to know why there is Table lock is been put even if query returns 1 record?

This is standalone test database, and my session is active. There is no other transaction happening in the db.
Comment
Watch Question

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
In SQL Server by default, a shared lock is placed on an object being queried.

As many shared locks can be placed on a table as there are queries running against it - but if an update comes along wanting an exclusive lock then it will wait until all shared locks are released.

There's some discussion about it here:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/65ae0e13-013f-4f3f-8c69-e1e9302b7804
http://support.microsoft.com/kb/271509
http://stackoverflow.com/questions/1017177/sql-server-select-statements-causing-blocking
http://ask.sqlservercentral.com/questions/32529/can-a-select-hold-an-exclusive-lock-on-a-table.html

I also wrote an article recently on how the READ COMMITTED isolation level works in SQL Server and comparing to the same isolation level in Oracle.   It may also shed some light on this, as what you're describing is the default behavior in the way that SQL Server implements READ COMMITTED isolation.

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_10950-READ-COMMITTED-Isolation-levels-A-comparison-between-Oracle-and-SQL-Server.html
There is no exclusive lock, I have created new database and inserted 10000 rows and when I ran select top 1 * from table why it's acquiring the table lock, ideally it should acquire a shared lock on particular row.

Correct me if I am wrong. Unless there are 5000 locks on the table, lock escalation manager will not do the lock escalation to table.
In my case, it should acquire only one shared lock on the table, then why  it puts the lock on the table.
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
What is the exact version of SQL Server you're running ?
What is the full output from sp_lock?  You're getting an X lock and not an IX or S ?
Have you altered the default isolation level ?
Are you running this in a transaction with other updates or stand alone ?

(I have tried to replicate your scenario, but the query completes much too fast for me to get any information back from sp_lock)
What is the exact version of SQL Server you're running ?
SQL SERVER 2008 R2
What is the full output from sp_lock?  You're getting an X lock and not an IX or S ?
spid     dbid   ObjId          IndId Type Resource Mode Status
 67       10      0                 0      DB                      S GRANT
 67        1     1131151075  0      TAB                     IS GRANT
Have you altered the default isolation level ?
READ committed
Are you running this in a transaction with other updates or stand alone ?
no,only one select statement.

Repro:
1:open ssms ,ctrl+n
2.run below query
select top 1 * from table1

sp_lock 66;
here my spid is 66 and I get below output
spid     dbid   ObjId          IndId Type Resource Mode Status
 67       10      0                 0      DB                      S GRANT
 67        1     1131151075  0      TAB                     IS GRANT
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
The lock is not an exclusive lock.  Type IS is an Intent Shared lock.

See this document for information on Lock Types in SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx

In order to maintain the READ COMMITTED isolation level as implemented by SQL Server, the engine needs to place shared locks on data being queried to protect it in the event that something comes along and tries to update it while it's actively being queried.

This is normal behavior for SQL Server under the default Isolation Level (if you haven't done so, read the article I linked in my first reply, I went into a fair amount of detail on how READ COMMITTED is implemented in SQL Server).

Mladen Prajdic also covers this aspect on his post here: http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Because of the pessimistic concurrency model that SQL Server uses (as opposed to the optimistic model used in Oracle), this is how it has to work.

This kind of lock will not prevent other readers from reading the data should they be querying it at the same time.  It will however, escalate into an exclusive lock should something come along and try to update the data while the query is running.
Actually i'm try to test, under how many minimum row locks requires for lock escalation manager to escalate from row level lock to table lock.

I'm trying to capture this information. Does any one know, how get this details practically?

I tried running in the below command to capture the information

begin transaction
 waitfor delay '00:00:05'
 select top 5001 *  from Sessions
 waitfor delay '00:00:20'
 commit transaction

but it's too fast to capture. And it gives me the output like below

spid dbid ObjId IndId Type Resource Mode Status
 67    10       0       0    DB                    S   GRANT
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.