none
Lock a table in SQL Server when user is reading it RRS feed

  • Question

  • As per business requirement when an  external application reads a table in SQL Server 2017 , then from that instance of time till the read is complete for the whole table no WRITE/READ  operation can be executed on the same table by other user .

    A weekly SQL job has been scheduled to populate the table (say dbo.X) which will run once in a week . It will hold huge amount of record.(Approx 1000K)

    The external system will query the table to read all the data from the table .

    Note:- The application can read the table at any instance of time.

    Please suggest for the above .

    Monday, July 13, 2020 9:59 PM

All replies

  • One way to to add a TABLOCKX hint to the SELECT query:

    SELECT *
    FROM dbo.Foo WITH (TABLOCKX);

    Note, however, this will not prevent queries using the READ UNCOMMITTED isolation level or NOLOCK hints from reading from the table.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, July 13, 2020 10:17 PM
  • Hi SubhadipRoy,

    Agreed with Dan, you could use TABLOCKX then other transactions could not read, update or delete this table.

    You could also add one transaction if necessary as below example:

    BEGIN TRANSACTION 
    BEGIN TRY
    Select * From Table with (TABLOCKX)
    Insert Into Table Values(@newValue)
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
    -- any other error handling goes here
    END CATCH

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 14, 2020 1:58 AM
  • Thanks

    Just to confirm the over all under standing of using TABLOCKX hint and the business requirement.

    SQL Job 1 - Write the data into the table dbo.x and while writing it has to ensure the table is locked. It will be scheduled once in a week . 

    There will be another external application which will run any time based on the need and when it try to read the table dbo.X then no READ/WRITE operation can be initiated at the same time by other user .

    To confirm TABLOCKX  hint will be used by the external system while reading the data from the table.

    Please confirm the above understanding 

    Tuesday, July 14, 2020 7:44 AM
  • Hi SubhadipRoy,

    It could be better to add one transaction to the SQL Job 1 although it will apply for an exclusive lock while waiting data into the table.

    You could use TABLOCKX  hint for the external system while reading the data from the table.

    Please go ahead and let us know if any update or concern. Thanks.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, July 14, 2020 8:49 AM
  • As I understand it, the best would be to use snapshot isolation. With snapshot isolation, there is no locking at all. The external process starts a transaciotn with snapshot isolation, and if other processes go and modify data while the external process is reading does not matter. The external process will see the data in the database in a consistent of view of the time the transaction started.

    To enable snapshot isolation, you need to run

    ALTER DATABASE db SET ALLOW_SNAPSHOT_ISOLATION ON

    This will incur an increased load on tempdb, as this is where SQL Server maintains the version store, which is used to support the snapshot ability.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 14, 2020 9:02 AM
  • Hi SubhadipRoy,

    If it is allowed to read slightly older versions of data from your external system, agreed with Erland, you could also consider to use row versioning and snapshot isolation.

    If it is not allowed, you could still consider TABLOCKX  hint instead.

     

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, July 14, 2020 9:34 AM
  • This is not an ideal situation where you want to lock the entire table while Reading Operations are performed by any user. What is the business logic ? How long the Table Lock is allowed ? While duration of Query Run or while user finishes using the data read operations ? 

    Table Lock hint is useful for Bulk Load or  Sequential Insert operations.

    Tuesday, July 14, 2020 11:37 AM
  • You can user the TABLOCK hint in your UPDATE or INSERT statement
    Tuesday, July 14, 2020 1:01 PM
  • Thank you

    It is one of the business need when an external system read the table to copy the data then no  other user will be able to  READ/WRITE the data from that instance of time till the end until the copy is completed.

    I believe when the external system read the data then it should execute the below T-SQL from their side

    SELECT column1, column2  FROM dbo.X WITH (TABLOCKX)

    Tuesday, July 14, 2020 1:46 PM
  • If you want to all other processes to disallow even reading the data from the table in question until the whole process is completed, then I think you can start the process by changing the current schema of the table to some temp schema, do your operation, change schema back.

    This way all other applications that attempt to read from the original table will get an error that table doesn't exist.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 14, 2020 2:39 PM
    Moderator
  • Hi SubhadipRoy,

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding! If you have any question or update, please feel free to let me know.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 15, 2020 2:42 AM
  • Can the scenerio be handled in the below way.

    1. use  DMV query to check if any user is performing READ/WRITE action on the table using job 1 which runs every 1 minutes and if the DMV query confirms any action is happening , it will return flag based on which job 2 will be disabled. 

    2. Once the above action is done , it will enable job 2 and run it

    Can you share any DMV query for the same .

    Wednesday, July 15, 2020 12:27 PM
  • Can the below query be configured in SQL agent job which runs every 1 minutes to check the relevant table is used in current query and if so disable the second Job and once table is no longer used in the current query enable the second job .Please suggest

    SELECT      r.start_time [Start Time],session_ID [SPID],
                DB_NAME(database_id) [Database],
                SUBSTRING(t.text,(r.statement_start_offset/2)+1,
                CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
                THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
                ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
                END) [Executing SQL],
                Status,command,wait_type,wait_time,wait_resource,
                last_wait_type
    FROM        sys.dm_exec_requests r
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
    WHERE       session_id != @@SPID -- don't show this query
    AND         session_id > 50 -- don't show system queries

    AND status='runnable'

    ORDER BY    r.start_time

    Wednesday, July 15, 2020 2:10 PM
  • Please confirm the above understanding
    Wednesday, July 15, 2020 3:37 PM
  • Please confirm the above understanding

    It seems utterly complicated for something that can be done with snapshot isolation. Then you don't have to bother whether other processes are accessing table or not. The external process will get a consistent view of the table. (Which is the only reasonable interpretation of the requirement.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 15, 2020 10:24 PM
  • Hi Subhadip,

    There are some more tips for you:

    1. Consider changing the database mode to single user before the populating the table job and change back to mutiple user once completed.

      ALTER DATABASE DATABASENAME SET SINGLE_USER

      ALTER DATABASE DATABASENAME SET MUTI_USER

    2. Consider changing the scheduled time of populating the table job to non-business time.

    3. Use a DMV query to check if any user is performing READ/WRITE action on the table using job 1 is theoretically feasible.


    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 16, 2020 3:18 AM
  • Thank you for all for the valuable suggestion.

    The External system(Job 2) will perform the READ operation and it will not update or change the schema of the table .

    All the business requirement need to be handle by  SQL Job 1. It will check what queries are currently running in the particular user database  

            DB_NAME(database_id) [Database],
                SUBSTRING(t.text,(r.statement_start_offset/2)+1,
                CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
                THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
                ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
                END) [Executing SQL],
                Status,command,wait_type,wait_time,wait_resource,
                last_wait_type
    FROM        sys.dm_exec_requests r
    OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
    WHERE       session_id != @@SPID -- don't show this query
    AND         session_id > 50 -- don't show system queries

    and if the above DMV return the query which has the table name involve it will disable it.

    To confirm in the Status column of the above query sometimes it shows "RUNNABLE","RUNNING","SUSPENDED"

    Please suggest which all status should be consider to check whether the query is running .

     

    Thursday, July 16, 2020 1:42 PM
  • Hi SubhadipRoy,
    If relevant table is only for report purpose, you do not need to lock it as you can easily copy the whole table as a backup.

    Many Thanks & Best Regards, Jackson Chen

    Thursday, July 16, 2020 3:44 PM
  • Hi SubhadipRoy,

    According to the sys.dm_exec_requests (Transact-SQL), status of quest could be one of the following:

    Background
    Running
    Runnable
    Sleeping
    Suspended

    Running: This status type defines that request is processing by CPU.

    Runnable: This status type can be simply defined as a request which is waiting in the CPU queue for running. 

    Suspended: This status type defines the waiting process of some resource. It can be I/O, LOCK, and LATCH etc.

    Then you could only consider Running status which is actually running.

    Best regards,

    Melissa

    -------------------------------------------

    MSDN Community Support

    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 17, 2020 1:16 AM