Solved

I like to want lock will held for the specific query

Posted on 2013-01-04
7
257 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.
0
Comment
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38744900
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.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_10950-READ-COMMITTED-Isolation-levels-A-comparison-between-Oracle-and-SQL-Server.html
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 38745556
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.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38745711
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)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 38745941
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
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38745966
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.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 38745975
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
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38746002
According to the documentation:
http://msdn.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx

The default is 5000 row locks.

There's a blog post here where someone works through an example:
http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/291/sql-server-2008-lock-escalation-explained

This is a little out of my area of expertise, but it's possible that IS (Intent Share) locks (as in your select statement) are not escalated whereas the X locks made in the update do actually make the escalation happen.

Play with the example in the blog post and see where that gets you.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server insert 12 30
SQL Insert parts by customer 12 33
Return 0 on SQL count 24 30
return table in table valued function  using dynamic sql, SQlServer 2008r2 5 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question