SELECT and Locking

  • Kendra Little

    SSC Enthusiast

    Points: 117

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

  • Grant Fritchey

    SSC Guru

    Points: 396385

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • RandMan7557

    Valued Member

    Points: 60

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

  • trowley

    SSC Veteran

    Points: 268

    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

    SSC Eights!

    Points: 881

    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: 42037

    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: 996088

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 71686

    Interesting question, thanks Kendra.

    good discussion, though...

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

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

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