none
TSQL Table Locks During Transaction... Probably.. Maybe...

    Question

  • After much searching, reading, re-reading and further searching and reading - I find myself at a dead end.
    With so little detail (at least that I can find/understand) from msdn articles and BOL, I throw myself humbly at the mercy of those better experienced to help solve the problem I face. And to any of those better equipped persons that donate their time to help me, useful or not, I sincerely thank you in advance.

     I trust you will forgive my likely naive attempt to explain the problem...
     If I could talk to my SQL server directly, which I often do, and have it actually understand me, which it often doesnt, I would say...

     "Please can you stop anything from changing the data i'm about to work with and dont let anything add a record related to those that i'm about to work with... but if anyone wants to read the data while i'm working thats ok."

    Essentially then, I think i'm looking for a way to make a table readonly for the duration of my stored procedure transaction.

    The basic flow of my sp is:

    BEGIN TRAN

    -- Take a copy of the data I want to work with (ok for people to read the actual data)
    CREATE TABLE #TempTable (RowId int identity(1,1), Table1_PrimaryKey int, KeepRow bit)
    INSERT INTO #TempTable (Table1_PrimaryKey, KeepRow)
    SELECT Table1_PrimaryKey, 0 
    FROM Table1
    WHERE (stuff that decides which rows I want)

    Start loop on records In #TempTable 

    IF (i want this row) BEGIN
        UPDATE #TempTable SET KeepRow = 1
    END

    Carry on looping til done... then...

    DELETE FROM Table1
    WHERE Table1_PrimaryKey IN (
        SELECT Table1_PrimaryKey FROM #TempTable WHERE KeepRow = 0
    )

    COMMIT TRAN

    END TRAN

    The bit thats making my head spin is that all relevant rows I have selected to start with form a kind of set. They are related by a foreign key to a parent table. I need to make sure that not only are the rows I am working with not modified or deleted - but no new rows can be added to Table1 (related to the same parent object) while im working.

    It is however, safe for people to look at the rows while im deciding about them (as I have copied them into a temp table to work with).

    So.. kind of make table read-only while i'm working.
    It feels like locking of some kind should help here.... but i'm fearing a blanket TABLOCKX as I understand it will prevent prevent others reading the data while im deciding what to keep. Any ideas which locks are appropriate? Have I made any sense at all?

    Thank you again in advance - even if only for reading this far.

    • Edited by inex0rable Monday, December 20, 2010 10:37 AM
    Wednesday, December 15, 2010 10:38 PM

Answers

  • If you are willing to make the whole table read-only during this process, then the simplest way is to use TABLOCK and HOLDLOCK in your SELECT statement (note that that is TABLOCK, not TABLOCKX).  So the beginning of the process would look like

    BEGIN TRAN
    
    -- Take a copy of the data I want to work with (ok for people to read the actual data)
    CREATE TABLE #TempTable (RowId int identity(1,1), Table1_PrimaryKey int, KeepRow bit)
    INSERT INTO #TempTable (Table1_PrimaryKey, KeepRow)
    SELECT Table1_PrimaryKey, 0 
    FROM Table1 With (Tablock, Holdlock)
    WHERE (stuff that decides which rows I want)
    

    Then other processes would be able to SELECT from that table, but not allowed to do UPDATE, DELETE, or INSERT until your process either does a COMMIT or a ROLLBACK.

    Tom

    • Marked as answer by inex0rable Thursday, December 16, 2010 9:52 AM
    Thursday, December 16, 2010 12:11 AM
  • Just to explain in further detail:

     

    TABLOCK Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.

    TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.


    Please 'Mark as Answer' if found helpful - Chris@tier-1-support

    Tier 1 Support
    • Marked as answer by inex0rable Thursday, December 16, 2010 9:52 AM
    Thursday, December 16, 2010 12:22 AM

All replies

  • If you are willing to make the whole table read-only during this process, then the simplest way is to use TABLOCK and HOLDLOCK in your SELECT statement (note that that is TABLOCK, not TABLOCKX).  So the beginning of the process would look like

    BEGIN TRAN
    
    -- Take a copy of the data I want to work with (ok for people to read the actual data)
    CREATE TABLE #TempTable (RowId int identity(1,1), Table1_PrimaryKey int, KeepRow bit)
    INSERT INTO #TempTable (Table1_PrimaryKey, KeepRow)
    SELECT Table1_PrimaryKey, 0 
    FROM Table1 With (Tablock, Holdlock)
    WHERE (stuff that decides which rows I want)
    

    Then other processes would be able to SELECT from that table, but not allowed to do UPDATE, DELETE, or INSERT until your process either does a COMMIT or a ROLLBACK.

    Tom

    • Marked as answer by inex0rable Thursday, December 16, 2010 9:52 AM
    Thursday, December 16, 2010 12:11 AM
  • Just to explain in further detail:

     

    TABLOCK Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.

    TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.


    Please 'Mark as Answer' if found helpful - Chris@tier-1-support

    Tier 1 Support
    • Marked as answer by inex0rable Thursday, December 16, 2010 9:52 AM
    Thursday, December 16, 2010 12:22 AM
  • Thank you for your time Tom. This certainly sounds like the one.

     

    Thursday, December 16, 2010 9:54 AM
  • ... and thank you for your clarification chris. I was indeed unsure as to the difference.
    Thursday, December 16, 2010 9:56 AM