locked
How to lock? RRS feed

  • Question

  • Here is my code:


    my_stored_procedure_for_updating_xml_column ( @userID UNIQUEIDENTIFIER; @logText VARCHAR(100) ) BEGIN -- Get xml from usertable DECLARE @myXml XML; SELECT @myXml = MyXml FROM tblUser WHERE UserID = @userID; -- Modify the xml SET @myXml.modify('insert <MyLogRow>{sql:variable("@logText")}</MyLogRow> as first into (/LogRows)[1]'); SET @myXml.modify('replace values of some nodes etc...); -- Save changes back to the user-table UPDATE tblUser SET MyXml = @myXml WHERE UserID = @userID;


    FLOW:
    1. Lets say USER 1 gets the xml ands starts modifying it
    2. USER 2 then gets the xml, before changes are saved back to tblUser by USER 1
    3. USER 1 is updating tblUser with its modified xml
    4. USER 2 is updating tblUser with its modified xml

    Result: Changes by USER 1 will be gone, because USER 2 is overwriting them!

    How to solve this? Do I need some kind of lock?

    Thursday, April 24, 2014 9:44 AM

Answers

  • So if I use UPDLOCK and have a lock on the row, is it then possible for other stored procedures to read the xml if they use SELECT-statements without UPDLOCK?

    Correct.

    What happens when the second user is "blocked", will it just be a delayed experience for the enduser (because his transaction needs to wait for the first transaction to commit/rollback), or will the procedure be canceled?

    Depends on how patient the user and the API is. And for that matter if you have fiddled with SET LOCK_TIMEOUT.

    By default, SQL Server will wait until the lock is released, although you can override this with SET LOCK_TIMEOUT. Many API has a command timeout, which typically is set to 30 seconds by default, which means that if SQL Server does not respond in that timeframe, they will tell SQL Server to stop running. (They don't know if SQL Server is running your query or waiting for a lock to be released.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Elvis Long Monday, April 28, 2014 2:44 PM
    • Marked as answer by Kalman Toth Saturday, May 3, 2014 8:28 PM
    Thursday, April 24, 2014 10:08 PM

All replies

  • You can use BEGIN TRAN ... COMMIT TRAN to lock the users but do the users use XML on the application or directly from SQL Server?  

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by HimanshuSharma Thursday, April 24, 2014 10:06 AM
    • Unproposed as answer by Yoda-1 Friday, April 25, 2014 7:48 AM
    Thursday, April 24, 2014 9:46 AM
    Answerer
  • Is BEGIN TRAN..COMMIT TRAN enough?

    The xml comes from a column in tblUser as you can see in my code.

    Thursday, April 24, 2014 9:56 AM
  • Is BEGIN TRAN..COMMIT TRAN enough?

    The xml comes from a column in tblUser as you can see in my code.

    yes thats enough

    Under default ISOLATION LEVEL which is READ COMMITED until the xml value modification is complete (commit/rollback) by one user, xml wont be available for the next user for data modification.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, April 24, 2014 10:05 AM
  • No, BEGIN / COMMIT TRANSACTION is not enough. The SELECT statement takes a shared lock which is released.

    You would need to do:

    SELECT @myXml = MyXml FROM tblUser WITH (UPDLOCK)
    WHERE UserID = @userID;

    Now you have taken an update lock on the row. This lock is compatible with shared locks, but not other update locks. Thus, if there two concurrent users, the guy that comes second, will be blocked. Not that this is only an issue if they use the same @userID.

    Then again, you could do it all in a single statement:

    UPDATE tblUser
    SET    MyXml.modify('....')
    WHERE  UserID = @UserID


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, April 24, 2014 3:09 PM
    Thursday, April 24, 2014 10:39 AM
  • Thank you,

    Question 1:
    "This lock is compatible with shared locks, but not other update locks."

    So if I use UPDLOCK and have a lock on the row, is it then possible for other stored procedures to read the xml if they use SELECT-statements without UPDLOCK?

    Question 2:
    "Thus, if there two concurrent users, the guy that comes second, will be blocked"

    What happens when the second user is "blocked", will it just be a delayed experience for the enduser (because his transaction needs to wait for the first transaction to commit/rollback), or will the procedure be canceled?

    Thursday, April 24, 2014 2:41 PM
  • So if I use UPDLOCK and have a lock on the row, is it then possible for other stored procedures to read the xml if they use SELECT-statements without UPDLOCK?

    Correct.

    What happens when the second user is "blocked", will it just be a delayed experience for the enduser (because his transaction needs to wait for the first transaction to commit/rollback), or will the procedure be canceled?

    Depends on how patient the user and the API is. And for that matter if you have fiddled with SET LOCK_TIMEOUT.

    By default, SQL Server will wait until the lock is released, although you can override this with SET LOCK_TIMEOUT. Many API has a command timeout, which typically is set to 30 seconds by default, which means that if SQL Server does not respond in that timeframe, they will tell SQL Server to stop running. (They don't know if SQL Server is running your query or waiting for a lock to be released.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Elvis Long Monday, April 28, 2014 2:44 PM
    • Marked as answer by Kalman Toth Saturday, May 3, 2014 8:28 PM
    Thursday, April 24, 2014 10:08 PM