I like to want lock will held for the specific query
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.
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
Steve Wales
8/22/2022 - Mon
Steve Wales
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.
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.
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 Wales
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 Wales
The lock is not an exclusive lock. Type IS is an Intent Shared lock.
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).
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.
VIVEKANANDHAN_PERIASAMY
ASKER
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
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