Solved

I like to want lock will held for the specific query

Posted on 2013-01-04
7
252 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now