SELECT and Locking

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

  • 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

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

  • 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?

  • 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.

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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