locked
Exclusive rows on transaction with UPDLOCK RRS feed

  • Question

  • Hi all,

    Inside an sql stored procedure, we have a transaction where we need to achieve the following:

    1. Select a set of data by joining a few tables based on search criteria and save that set of data in a temp table.

    2. From that set of data, do some further processing to the data that is not possible to perform on a simple select, which includes processing row by row on the temp table (perform some calculations, comparisons and at the end decide if we actually want to consider that row or not).

    3. At the end, from the original set of data, only a subset will be choose and then an update will be made to the state of a table based on that subset.

    This stored procedure may run concurrently, and we want to ensure that the first person who makes the call reserves temporarily the records that are being processed, and if in the meantime someone else attempts to run the same procedure, even if it attempts to select some or all the same data, it cannot run until all previous work is done. So we want to lock the rows (even for select) but we don't want to lock the entire table.

    What we are thinking to use is, on the first query that selects the set of data (1), apply a WITH(UPDLOCK) on the table we want to 'reserve' records. After that points (2) and (3) are run and if everything goes well commit is executed so records are updated as required.

    On a medium-level analysis we don't see any reason to avoid this, but should we be aware of any unexpected behavior?

    Should we be aware of any unexpected behavior?

    We were wondering if applying one isolation level specifically on this procedure could help achieve this, but until the moment we don't see if it is feasible.

    Thank you.




    • Edited by Pakojones Tuesday, February 14, 2017 10:33 PM Aditional text
    Tuesday, February 14, 2017 9:59 PM

Answers

  • >So using the app lock mechanism it should be used in these stored procedures that are doing almost the same thing with the same lock name correct?

    Yes.

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by Pakojones Thursday, February 16, 2017 4:03 PM
    Thursday, February 16, 2017 3:45 PM

All replies

  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

    ; GO BEGIN TRANSACTION; GO <your operation put here> GO COMMIT TRANSACTION; GO

    From MSDN regarding SET TRANSACTION ISOLATION LEVEL:

    SERIALIZABLE

    Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

    So your transaction maintains all locks throughout its lifetime-- even those normally discarded after use. This makes it appear that all transactions are running one at a time, hence the name SERIALIZABLE. Note from Wikipedia regarding isolation levels:

    SERIALIZABLE

    This isolation level specifies that all transactions occur in a completely isolated fashion; i.e., as if all transactions in the system had executed serially, one after the other. The DBMS may execute two or more transactions at the same time only if the illusion of serial execution can be maintained.


    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

    Wednesday, February 15, 2017 6:53 AM
    Answerer
  • Uri, thanks a lot for your reply, I found it very helpful, but let me see if I'm understanding this correctly - in a query like this:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    BEGIN TRANSACTION
    
    SELECT T1.ID, T2.ID, T3.ID
    	FROM TABLE1	T1
    	JOIN TABLE2 T2 ON T1.ID = T2.T1ID
    	JOIN TABLE3 T3 ON T2.ID = T3.T2ID
    
    COMMIT TRANSACTION

    While the transaction is not commited/rollback, will lock the involved records on TABLE1, TABLE2 and TABLE3 correct?

    Wednesday, February 15, 2017 10:02 AM
  • Yes, UPDATE/DELETE/INSERT will be blocked on those tables

    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

    Wednesday, February 15, 2017 10:08 AM
    Answerer
  • While the transaction is not commited/rollback, will lock the involved records on TABLE1, TABLE2 and TABLE3 correct?

    As Uri mentioned, modifications will be blocked. I want to add that you'll likely get a subsequent deadlock if 2 different sessions contend for the same data. You could specify an UPDLOCK to avoid the deadlock.

    Another option is to use an application lock to serialize execution. This can be done in the default READ COMMITTED isolation level and without locking hints. For example:

    SET XACT_ABORT ON;
    SET NOCOUNT ON;
    
    BEGIN TRAN;
    
    --acquire lock to serialize concurrent execution
    EXEC sp_getapplock
    	  @Resource = 'MyResource'
    	, @LockMode = N'Exclusive';
    
    --DML here
    
    --commit transaction and release app lock
    COMMIT;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, February 15, 2017 12:20 PM
  • SERIALIZABLE will not work here.  It doesn't do what it sounds like, and should generally be avoided.

    +1 for sp_getapplock, if you only need to block other sessions running the same procedure.  And otherwise UPDLOCK.  But handling concurrency with row locking is fraught with complexity.  For instance locks apply only to the data structures your query plan actually reads.  So if you have multiple indexes that could satisfy the UPDLOCK query, it's possible for them to run concurrently.

    David


    Microsoft Technology Center - Dallas

    My Blog

    • Proposed as answer by Naomi N Wednesday, February 15, 2017 11:09 PM
    Wednesday, February 15, 2017 2:49 PM
  • Following Dan's reply - "As Uri mentioned, modifications will be blocked. "

    My problem is that I want to block the possibility of selecting those records.

    I was trying to avoid adding an initial update to a state column to 'reserve' records (so no other can get them because the state in the meantime has changed) and only after all processing is done update state column again to tell which records are actually 'free' (and can be selected by other users after that) or actually 'selected' exclusively for the first user.

    Wednesday, February 15, 2017 11:03 PM
  • >My problem is that I want to block the possibility of selecting those records.

    Do you need to prevent all queries from selecting those records, or only other sessions running the same stored procedure?

    David


    Microsoft Technology Center - Dallas

    My Blog

    Thursday, February 16, 2017 2:01 AM
  • My problem is that I want to block the possibility of selecting those records.

    The app lock will allow only one proc instance to run at a time.  I have the same question as David. 


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, February 16, 2017 2:36 AM
  • If possible, I would like to prevent concurrent users to use the same records, but allow them to run the same stored procedure.

    For example:

    Table with 10 records.

    In a scenario where we have 2 concurrent users, the normal behavior would be the first one runs the procedure, checks which of the 10 records must be assigned to him/her (it is not a simple select, is a bunch of code), and in the end 5 records are assigned to him/her.

    At the same time, second user checks the other 5 records and it is decided which will be assigned to him/her.

    So, there may be more than one user at the same time running the same procedure but data must be exclusive in a FIFO way.

    Thursday, February 16, 2017 10:42 AM
  • If possible, I would like to prevent concurrent users to use the same records, but allow them to run the same stored procedure.

    With the app lock, 2 different users can run the proc simultaneously but one will be blocked until the other commits so users would never see the same data. As long as the proc executes reasonably quickly, the short-term blocking shouldn't be noticeable to users and will not be prone to deadlock errors. This is the best approach if the queries are identical, like your example with no WHERE clause or a parameterized query with identical values.

    Specifying an UPDLOCK hint for each table will provide more concurrency if users typically select different rows using a parameterized query with differing values and the execution plan is such that same rows are not touched.  The UPDLOCK will serialize access to the requested rows so only one user could touch a given row at a time, even in the default READ COMMITTED isolation level, until the transaction commits. This method will block if attempt is made to touch the same row and may be prone to deadlocking depending on the execution plans.

    Note that neither of these methods will prevent other queries or stored procedures from reading data. That would require using an XLOCK hint instead of UPDLOCK.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, February 16, 2017 11:57 AM
  • I'm sorry I should have provided an example at the beginning of the topic.

    Let's assume a simple table like this:

    CREATE TABLE TMP (ID				INT						NOT NULL,
    									Name			VARCHAR(128)	NOT NULL,
    									Status		INT						NOT NULL,	--	0 (Free), 2 (Reserved)
    									UserName	VARCHAR(128)	NULL,
    	CONSTRAINT PK_TMP PRIMARY KEY (ID)
    )
    
    INSERT TMP (ID, Name, Status) VALUES (1, 'TEST1', 0)
    INSERT TMP (ID, Name, Status) VALUES (2, 'TEST2', 0)
    INSERT TMP (ID, Name, Status) VALUES (3, 'TEST3', 0)
    INSERT TMP (ID, Name, Status) VALUES (4, 'TEST4', 0)

    I have two users, and I want that the first one takes the first two records, and the other takes the remaining two.

    The code I need to run (apart from the row exclusivity) is the following:

    BEGIN TRY
    	BEGIN TRAN
    		
    		DECLARE @Items TABLE (ID				INT,
    													RowNumber INT,
    													Selected	BIT)
    		
    		--	1. Choose rows for processing (my real scenario involves a few JOINS including tables with thousands of records related by foreign key) and a complex WHERE clause
    		INSERT @Items (ID)
    			SELECT ID
    				FROM TMP
    				WHERE Status = 0	--	Free
    			
    		--	2. Rows processing
    		UPDATE @Items
    			SET RowNumber = UI_RN.RowNumber,
    					Selected = CASE WHEN UI_RN.RowNumber <= 2	--	For the sake of simplicity. My real case requires a recursive query, while or cursor to perform a dependency between rows in order to determine which rows will be considered (reserved) and which rows will not be considered (remain free)
    													THEN 1 
    													ELSE 0 
    											END
    			FROM @Items UI
    			JOIN (SELECT UI.ID,
    									 ROW_NUMBER() OVER(ORDER BY UI.ID) RowNumber
    						  FROM @Items UI
    					 )							UI_RN ON UI.ID = UI_RN.ID
    		
    		--	3. Perform update to table and select records
    		UPDATE TMP
    			SET Status = 2,	--	Reserved
    					Username = 'MyUsername'
    			FROM TMP						TMP
    			JOIN @Items					U	  ON TMP.ID = U.ID
    														 AND U.Selected = 1
    			
    		COMMIT
    		
    	END TRY
    	BEGIN CATCH
    	
    		ROLLBACK
    		SELECT ERROR_NUMBER(), ERROR_MESSAGE()
    		
    	END CATCH	
    	
    	SELECT *
    		FROM TMP
    

    My problem is that I only can ensure the reserved records on section 3, so, if between section 1 and 3 the other user also executes the same procedure, it may be processing the same records, since no row lock is being made until section 3 is executed.

    What I'm trying to achieve with this example is when first user executes, the second user won't get the same rows when it arrives section 1.

    Thursday, February 16, 2017 12:13 PM
  • It seems to me the app lock will address your requirements.

    I suggest you add SET NOCOUNT ON, and more importantly, SET XACT_ABORT ON to the proc. I think it's an odd (if not dangerous) pattern to catch the error without re-throwing, returning error info as a result set. Consider this alternative error handler to throw the error:

    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
    	THROW;
    END CATCH;

    The handler below may be used in pre-SQL 2012 versions to re-throw errors, although the error will be a user-defined 50000 error rather than the original one:

    BEGIN CATCH
    
        IF @@TRANCOUNT > 0 ROLLBACK;
    
    	DECLARE
    		  @ErrorNumber int = ERROR_NUMBER()
    		, @ErrorMessage nvarchar(2048) = ERROR_MESSAGE()
    		, @ErrorSeverity int = ERROR_SEVERITY()
    		, @ErrorState int = ERROR_STATE()
    		, @ErrorLine int = ERROR_LINE();
    
    	RAISERROR('Error %d caught at line %d: %s'
    		, @ErrorSeverity
    		, @ErrorState
    		, @ErrorNumber
    		, @ErrorLine
    		, @ErrorMessage);
    
    END CATCH;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Thursday, February 16, 2017 1:15 PM grammar
    Thursday, February 16, 2017 1:13 PM
  • Yep. app lock.  Especially because

    >my real scenario involves a few JOINS including tables with thousands of records related by foreign key) and a complex WHERE clause

    So relying on row locking to control concurrency is problematic where you have complicated plans.

    One other handy thing about app locks too is that you can release the app lock before the end of your transaction, if you want.  So if this gets called in the context of a longer transaction you can allow other sessions to procedd after

    UPDATE TMP SET Status = 2, -- Reserved

    David


    Microsoft Technology Center - Dallas <p></p> <a href="http://blogs.msdn.com/dbrowne">My Blog</a>

    Thursday, February 16, 2017 2:41 PM
  • I've just realized that there is similar functionality involving the same tables in other stored procedures in the same database.

    So using the app lock mechanism it should be used in these stored procedures that are doing almost the same thing with the same lock name correct?

    Thursday, February 16, 2017 3:38 PM
  • >So using the app lock mechanism it should be used in these stored procedures that are doing almost the same thing with the same lock name correct?

    Yes.

    David


    Microsoft Technology Center - Dallas
    My blog

    • Marked as answer by Pakojones Thursday, February 16, 2017 4:03 PM
    Thursday, February 16, 2017 3:45 PM
  • Thank you guys for your help
    Thursday, February 16, 2017 4:03 PM
  • Just another question, as a doubt.

    sp_getapplock does what I need to do, but I don't want just to have a 'blind' semaphore to a stored procedure, I want something different - I want to be able to give sp_getapplock an ID which is a combination of two parameters that the stored procedure receives and apply the applock based on that ID (for my scenario this combination of parameters is what define the concurrency problem).

    From a perspective of pure development, I've tested the following side by side and it works.

    DECLARE @I INT = 1
    
    PRINT  CONVERT(VARCHAR, GETDATE(), 120) + ' AppLock for ID ' + CONVERT(VARCHAR(32), @i)
    
    DECLARE @appLockID VARCHAR(32) = 'AppLock_' + CONVERT(VARCHAR(16), @I),
    			  @result		INT;
    
    BEGIN TRAN
    		
    	EXEC @result = sp_getapplock @Resource = @appLockID,   @LockMode = 'Exclusive';  
    				
    	PRINT CONVERT(VARCHAR, GETDATE(), 120) + ' AppLock for ID ' + CONVERT(VARCHAR(32), @i) + ' getapplock result: ' + CONVERT(VARCHAR(32), @result)		
    					  
    	IF (@I = 1)
    	BEGIN
    
    		WAITFOR DELAY '00:00:05'
    	 
    	END
    
    	EXEC @result = sp_releaseapplock @Resource = @appLockID;
    
    	PRINT CONVERT(VARCHAR, GETDATE(), 120) + ' AppLock for ID ' + CONVERT(VARCHAR(32), @i) + ' releaseapplock result: ' + CONVERT(VARCHAR(32), @result)
    
    COMMIT

    If I open two query windows and declare @i as '1' in both, the first one runs and the second one waits until the first is done. TOP!

    On the second window, if I declare @i as '2' and run both queries side by side, each one does its work without need to wait, which is what I need.

    But there are lots of combinations of applock ID's that I need to apply in order to control concurrency. My doubt is how the diversity of sp_getapplock impact overall database performance or if there is some drawback that I'm not seeing now of using it like this that I should be aware?

    Thanks again.

    Friday, February 17, 2017 10:49 AM
  • But there are lots of combinations of applock ID's that I need to apply in order to control concurrency. My doubt is how the diversity of sp_getapplock impact overall database performance or if there is some drawback that I'm not seeing now of using it like this that I should be aware?

    sp_getapplock leverages the inherent locking mechanisms in SQL Server to acquire, evaluate, and release locks. The engine is optimized such that it can easily millions of concurrent locks.  I wouldn't be concerned about performance and overhead with only one app lock per connection.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, February 17, 2017 11:44 AM