Blocking updates of a table

Hot Topic

Setting Passwords In Connections Using Script Task
SSIS: Fpreach Loop Container- Execute SQL Task with ReturnValue Parameter
SET RECOVERY SIMPLE generates error
Asp.Net not finding the SQLServer for setting up Security problem
VS2005 and SQL Reporting Service
Configure Report Server with SQL Server Express with Advanced Services.
UK Datetime parameter problem SQL 2005 RS
"Package failed validation from the ExecutePackage task" error
Add scripttask script programmatically
Stored Procedure Not Retieving Data

SQL Server

Back to Parent Report button not visible
Establishing connection to SQL 2005 Server Express
Cannot complete Install of Express
unable to display images dynamically using parameters in expressions
How to Get detailed Logging info?
Suitability for Serach intensive web app
FREETEXTTABLE
Using DSO for parallel partition processing
missing notifications
Error trying to use Quick Find

Blocking updates of a table

Hi,

I've been trying to design a way for me to issue a transaction that:
  1. Block all inserts on a table when row X has a certain value (call it A)
  2. Add a row to the table with row X containing A
  3. Add rows to another table
  4. Unblock inserts
  5. Commit transaction
Is this possible Can anyone give me some pointers as to what to do

Thanks in advance!

Answer this question

Blocking updates of a table

  • ljlevend

    For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly

    sunil> yes

    Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table

    sunila> if you only want to block inserts by other transactions under the condition you have mentioned but allow updates/selects, then there is no way.


    thanks, 

  • lexk

    For 3 that is what I meant.

    For 1 and 2, it sounds like TABLOCKX locks the whole table exclusively (from what I can find in Books Online) - have I read it correctly Is there any way to block other transactions from inserting rows (while the first transaction is not yet commited) when a table key column (A in my original post) is a certain value (X in my original post) - rather than just locking the whole table

    Thanks for your help!

  • RizwanSharp

    I assume you only want to block Inserts but not Updates, Deletes and SELECTS.

    You could try using an Insert trigger that would rollback the other transactions until a condition has been achieved.

  • MLR

    For (1) and (2)
    While inserting the row that has a certain value (A), you can request a TABLOCKX. This will acquire X lock on the table and there by block other inserts by other concurrent transactions.

    For (3): Is it like any other insert to another table In that case, nothing special needs to be done

    Thanks
    Sunil Agarwal



  • Blocking updates of a table
Answer this question