Post reply

Table Lock

  • May 13, 2003 at 10:18 am

    #81902

    I want to lock down a table during 2 separate sequential SQL statements (insert, update - respectively). Has anyone had experience with the syntax of this nestled in a stored procedure. Trying to code an atomic step.

  • May 13, 2003 at 12:36 pm

    #457014

    I think this can be done by using locking hints for the update/insert statements...

    To use the table hints the insert/update statements within the stored procedure can be written as :

    Insert Table1 WITH (TABLOCKX) (col1,Col2)

    Values(1,2)

    Update Table1 WITH (TABLOCKX) set Col1 = 10

    This link provides more info on table hints

    http://www.sql-server-performance.com/rd_table_hints.asp

  • May 13, 2003 at 12:45 pm

    #457018

    Let SQL Server do what it's been designed for. Use a BEGIN TRAN / COMMIT TRAN block around the steps, with ROLLBACK checks after each step. This will ensure ACID functionality for transactions without having to get all scrambled up in TABLOCK and other locking hints best left to SQL Server to deal with.

    Just trying to save you a headache,

    Jay

  • May 13, 2003 at 12:52 pm

    #457019

    very true... I missed the obvious out here...

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

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