SELECT and Locking

Question

SELECT and Locking

Kendra Little

SSC Enthusiast

Points: 147
More actions
February 18, 2020 at 12:00 am

#3725215

Comments posted to this topic are about the item SELECT and Locking

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Mmmmm... Not digging that answer, but I'm not stupid enough to start an argument with Kendra.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RandMan7557 Valued Member Points: 60 More actions · Answer 2

A very short time is not what I would consider a precise answer . . .

trowley Mr or Mrs. 500 Points: 521 More actions · Answer 3

From

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15

READ COMMITTED

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

As the question is not specific to the shared lock type, I assumed the default settings.

While A is factually True, Could it not be argued that B and/or C are also True?

darrenjacquet SSCommitted Points: 1646 More actions · Answer 4

I like the questions that make you think a little. No pun intended.

From: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15

Shared Locks

Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic 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.

Bold added by me. I understand that this may not have been documentation considered when this question was formulated but to me this statement seems to imply that B) may have been the better choice of answer.

At any rate I guessed that the intent was that the lock would be held for as short a time as possible and chose correctly. Thank you for making me think on a Tuesday morning. Good day everyone.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Not to be rude, but I'd have to agree with Trowley here. While during a scan you may see individual S locks page locks come and go quickly on the table being read, there is a persistent IS type lock on the object itself.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

Grant Fritchey wrote:

Mmmmm... Not digging that answer, but I'm not stupid enough to start an argument with Kendra.

Roger that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stewart "Arturius" Campbell SSC Guru Points: 72624 More actions · Answer 7

Interesting question, thanks Kendra.

good discussion, though...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”