I have a table that holds metadata for "documents", let's call it DOCS. Other tables point to the metadata table's ID, to build the parts of the document.
I want to make sure that, in my application, I can have one person "check out" a document so that they are the only person who can edit it. (With an override mechanism built into the app, of course).
So my idea is to have a column in the DOCS table, LockedBy, which holds a user ID if the document is locked.
I want the application to only have to use one stored procedure for inserts and updates, so I'm currently taking the given @DOCID and doing an "upsert":
UPDATE DOCS SET column=@param WHERE ID=@DOCID; IF @@ROWCOUNT=0 --@@ROWCOUNT will be 0 if the update fails BEGIN INSERT INTO DOCS(columnnames) VALUES(@newvalues); SET @DOCID=SCOPE_IDENTITY(); END
But now that I want to add the locking mechanism, I need some way to check if the doc exists, and it is not locked by someone else, taking into account the possibility of concurrency.
If I do:
BEGIN TRANSACTION TR1 IF EXISTS( SELECT * FROM DOCS WITH(HOLDLOCK,ROWLOCK) WHERE ID=@DOCID) BEGIN IF NOT EXISTS(SELECT * FROM DOCS WHERE ID=@DOCID AND LockedBy=@Editor) RETURN -1 --return "error" code to app END ELSE --create row here --finally do all the updates to secondary tables COMMIT TRANSACTION TR1
- Will the row be locked from the
SELECTto the end of the transaction? And if I create the row in the last
ELSEclause, do I need to do something like
INSERT INTO DOCS WITH(HOLDLOCK,ROWLOCK)?
- Is there a better way to do this?