Post reply

How to acquire a lock on table for specified time

  • August 22, 2012 at 2:14 am

    #263207

    Hi,

    How to place a lock on database table for specified time and release the lock after specified time is complete?

    Regards,

    Narasimhulu

  • August 22, 2012 at 2:19 am

    #1528494

    you can use sp_getapplock. Check out the following link:

    http://msdn.microsoft.com/en-us/library/ms189823.aspx

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 22, 2012 at 2:32 am

    #1528500

    Hi,

    Thanks for your reply.

    I tried to hold the lock on a table using sp_getapplock.

    But after placing lock, it still allowing to perform INSERT or UPDATE operaion on table.

    I do not want to allow these operation during holding the lock on table.

    Please suggest

    Thanks & Regards,

    Narasimhulu

  • August 22, 2012 at 3:19 am

    #1528526

    try using this:

    BEGIN TRAN

    SELECT *

    FROM test_table (tablockx)

    WHERE col1 = '1'

    WAITFOR DELAY '00:02:00'

    ROLLBACK TRAN

    GO

    change your table name (table_name) and the time you want to obtain the lock in WAITFOR statement. Let me know if this helps!

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 22, 2012 at 4:07 am

    #1528541

    l.narasimha (8/22/2012)

    Hi,

    Thanks for your reply.

    I tried to hold the lock on a table using sp_getapplock.

    But after placing lock, it still allowing to perform INSERT or UPDATE operaion on table.

    I do not want to allow these operation during holding the lock on table.

    Please suggest

    Thanks & Regards,

    Narasimhulu

    Do give me the the code you are running for this. Also check my previous post.

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

Viewing 5 posts - 1 through 5 (of 5 total)

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