locked
setting exclusive lock RRS feed

  • Question

  • I want to set an exclusive lock on tables row ...how can be done the same..?

     plz repl.

     

    Thursday, February 9, 2012 12:31 PM

Answers

  • If you use a transaction with the isolation set to REPEATABLEREAD then all locks taken out will be held for the length of the transaction. You can commit the transaction a few hours later.

    If you do not want to hold all locks, just the locks on specific table, then use the example in my first answer, and again, commit the transaction a few hours later.

    Be aware, if you are doing this, depending on your code, you are significantly increasing the risk of deadlocks. A deadlock is where 2 transactions hold locks on different resources and they both need the other transactions resource to continue, meaning that neither can ever continue. If this happens, SQL Server will kill the least expensive transaction. If both transactions have the same cost, SQL Server will pick one at random and kill it.

    Hope that helps.


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    • Marked as answer by Manish Kohale Friday, February 10, 2012 1:14 PM
    Friday, February 10, 2012 12:31 PM

All replies

  • Hi,

    I may need a little more information about what you are trying to acheive, but at face value, within an Execute SQL Task, you could try...

    BEGIN TRANSACTION
    SELECT *
    FROM MtTable (WITH ROWLOCK, XLOCK, HOLDLOCK)
    WHERE PKCol = 5

    This will hold an xlock on the row that has a value of 5 in PKCol.

    Then, when you want to release, commit the transaction

    Remember, in order for the "native" transaction to span tasks, you need to set the retain same connection setting on the connection manager


    Peter Carter http://sqlserverdownanddirty.blogspot.com/


    Thursday, February 9, 2012 6:46 PM
  •  actualy i am developing window based application which will run on different location in LAN .and one record should be accesed at only one location on ane machin only.if one is updating the record that should not be modified by onather  machin. Any tips .I didn got 

    "Remember, in order for the "native" transaction to span tasks, you need to set the retain same connection setting on the connection manager" ..Pleas elaborate it little to me.

     
    Friday, February 10, 2012 5:40 AM
  • Hi,

    When I was talking about native transactions, I was refering to SSIS, because this thread is in a SSIS forum, but now you have elaborated, it sounds more like a DB Engine question. Can you confirm?

    If you only want one record accessed at any one time, you can just use the WITH(XLOCK) hint on your SELECT statements. Alternatively use the REPEATABLEREAD isolation level.


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Friday, February 10, 2012 11:29 AM
  •   how long can we have lock on the record..? i mean can i keep other from updating the record at other end for few ours..?

    Friday, February 10, 2012 11:46 AM
  • If you use a transaction with the isolation set to REPEATABLEREAD then all locks taken out will be held for the length of the transaction. You can commit the transaction a few hours later.

    If you do not want to hold all locks, just the locks on specific table, then use the example in my first answer, and again, commit the transaction a few hours later.

    Be aware, if you are doing this, depending on your code, you are significantly increasing the risk of deadlocks. A deadlock is where 2 transactions hold locks on different resources and they both need the other transactions resource to continue, meaning that neither can ever continue. If this happens, SQL Server will kill the least expensive transaction. If both transactions have the same cost, SQL Server will pick one at random and kill it.

    Hope that helps.


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    • Marked as answer by Manish Kohale Friday, February 10, 2012 1:14 PM
    Friday, February 10, 2012 12:31 PM
  • the below code is giving me error

    BEGIN TRANSACTION SELECT * FROM MtTable (WITH ROWLOCK, XLOCK, HOLDLOCK) WHERE PKCol = 5

    "Incorrect syntax near the keyword 'with'.

    If this statement is a common table expression or an xmlnamespaces clause,

    the previous statement must be terminated with a semicolon."

    any suggetion..?

    Monday, February 13, 2012 10:31 AM
  •  I got the answer for my above Question it was because of "With" was in brcaes it should be

    BEGINTRANSACTIONSELECT*FROMMtTable WITH(ROWLOCK,XLOCK,HOLDLOCK)WHEREPKCol =5

     how we can detect if the record has lock or not in sql server or in C# code..

    Plz rpl

    Monday, February 13, 2012 11:22 AM