Sql Server Management Studio: table locked due to select statement

Question

Given the following scenario: A select statement is executed from Sql Server Management Studio (2005, 2008) to retrieve a large resultset. This is not done by choosing 'Edit top 200 rows' from the context menu.

Our DBA says that it's possible this can lock the table being queried. Unlocking occurs when the query results are closed.

I can't find any documentation that confirms this is true. So, fact or fiction?

Edit:
Selected Aaron's answer, and thumbs up to Yuri and Lloyd for helpful answers.

Regards,

Michel

if it's only a select (reading data), as far as I know, there is no lock of the table — Gonzalo.-, Sep 06 '12 at 12:08
Access used to exhibit this behaviour when you had linked it to SQL Server tables. Maybe that's what they're thinking of? — Damien_The_Unbeliever, Sep 06 '12 at 13:52

score 2 · Answer 1 · answered Sep 06 '12 at 12:18

try to read this article. Lock Modes

Shared Locks

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. For more information, see Types of Concurrency Control. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

score 2 · Accepted Answer · answered Sep 06 '12 at 12:43

2

A SELECT is run once, and the shared locks (not exclusive locks!) are released once the data has completed being read. This may coincide precisely with the last row being displayed on the client, but it may occur a few milliseconds before. The fact that the results are open on the client is not known by SQL Server (unless we are talking about "Open Table" / "Edit Top 200 Rows"). I don't think it's possible with a single SELECT statement to lock the table in the way you describe - other readers and writers should be able to operate on the table just fine, though there may be some contention (not exclusive locking) while your SELECT is running.

A way you could simulate this, however, is to do some combination of...

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM dbo.table WITH (TABLOCKX);

...and then go to lunch.

answered Sep 06 '12 at 12:43

Aaron Bertrand

272,866
37
466
490

1

'...and then go to lunch', priceless! – Michel van Engelen Sep 07 '12 at 05:55
The table is locked for writing, if you are performing a select over all of its rows, because of the shared lock issued by default. However, the shared lock allows you to perform readings on other connections/transactions, unless a locking hint is used to perform an exclusive lock over it. – Guillermo Gutiérrez Oct 04 '13 at 21:28
@guillegr123 sure, but from the context of the question (over a year ago) it seemed to me that the DBA was implying that it would lock the table exclusively / block other readers. Why else would it come up in conversation? – Aaron Bertrand Oct 04 '13 at 21:35

score 2 · Answer 3 · answered Sep 06 '12 at 13:27

Your SELECT statement will lock the table so that changes can't be made to the table while your statement is running. It will not lock the table against other SELECT statements.

SELECT statements operate using shared locks. Shared locks are compatible with other shared locks. As a result, you can have multiple SELECT statements running against the same table. Any changes made to a table require an exclusive lock. A table must wait for all shared locks to end before it can grant a connection an exclusive lock. Thus, a transaction that makes changes to a table must wait until all other transactions on the table end.

Sql Server Management Studio: table locked due to select statement

3 Answers3