Post reply

Lock table for Insert/Update/delete

  • June 4, 2021 at 12:21 pm

    #3892021

    I am using SQL Sever 2008. I want to lock the whole table for insert/delete/update operation,but should allow other users to read.

    can anyone please suggest How to lock SQL table from insert/delete/update, but allow read , until certain operation is completed on that table ?

  • June 4, 2021 at 2:32 pm

    #3892074

    SQL will do the locking for you automatically, depending on what you update;

    SQL will figure out if it needs to lock a few rows, pages or even the whole table, depending on the operation and how much data you seem to be updating; the SQL engine issues "intent to exclusively lock" commands for any of those three things above . those locks make other processes wait for the update to complete if they want to query the data, or also insert/update/delete.

    there are always few edge cases where you might need to lock the whole table, but in general, you let SQL handle it; other processes, if they are using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, command, or the WITH (NOLOCK) hint, can still query  the data with dirty reads regardless of the lock.

    lookup WITH TABLOCKX (exclusive table lock)  or WITH UPDLOCK to see if that is really what you are after.

     

    are you unsure of the update and are second guessing the locking mechanism, or do you really need to lock the table?

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 4, 2021 at 3:00 pm

    #3892082

    Add WITH (TABLOCK) hint after the table name.

    Examples:

    UPDATE dbo.table_name WITH (TABLOCK)

    SET ...

    INSERT INTO dbo.table_name WITH (TABLOCK)

    SELECT ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

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