sp_getapplock to synchronize concurrent access to in-memory tables
I have about 20 stored procedures that consume each other, forming a tree-like dependency chain. The stored procedures however use in-memory tables for caching and can be called concurrently from many different clients. To protect against concurrent update / delete attempts against the in-memory tables, I am using sp_getapplock and SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;. I am using a hash of the stored procedure parameters that is unique to each stored procedure, but multiple concurrent calls to the same stored procedure with the same parameters should generate the same hash. It's this equality of the hash for concurrent calls to the same stored proc with the same parameters that gives me a useful resource name to obtain our applock against. Below is an example: BEGIN TRANSACTION EXEC #LOCK_STATUS = sp_getapplock #Resource= [SOME_HASH_OF_PARAMETERS_TO_THE_SP], #LockMode = 'Exclusive'; ...some stored proc code... IF FAILURE BEGIN ROLLBACK; THROW [SOME_ERROR_NUMBER] END ...some stored proc code... COMMIT TRANSACTION Despite wrapping everything in an applock which should block any concurrent updates or deletes, I still get error 41302: The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted. Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. Am I using sp_getapplock incorrectly? It seems like the approach I am suggesting should work.
The second you begin your transaction with a memory optimized table you get your "snapshot", which is based on the time the transaction started, for optimistic concurrency resolution. Unfortunately your lock is in place after the snapshot is taken and so it's still entirely possible to have optimistic concurrency resolution failures. Consider the situation where 2 transactions that need the same lock begin at once. They both begin their transactions "simultaneously" before either obtains the lock or modifies any rows. Their snapshots look exactly the same because no data has been modified yet. Next, one transaction obtains the lock and proceeds to make its changes while the other is blocked. This transaction commits fine because it was first and so the snapshot it refers to still matches the data in memory. The other transaction obtains the lock now, but it's snapshot is invalid (however it doesn't know this yet). It proceeds to execute and at the end realizes its snapshot is invalid so it throws you an error. Truthfully the transactions don't even need to start near simultaneously, the second transaction just needs to start before the first commits. You need to either enforce the lock at the application level, or through use of sp_getapplock at the session level. Hope this helped.
SSIS - move xml files to appropriate folder depends on Conditional Split result
SQL Server query not returning expected row [duplicate]
Get count of Items by customer, along with count of records with different item types in SQL Server 2014
After apply Ms17-010 database confused [on hold]
Registered Servers for Azure SQL Databases
Azure search return parent and child records
How to identify a long-running query in the SQL Server database?
How to exclude items in Visual studio SQL schema compare based on string pattern or alternative
Dapper not updating object in database
SQL Multi-condition WHERE clause with one optional parameter?
T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008
SQL Server - Where CONTAINS (Field, SUBQUERY)
How to include Null value in Join?
SQL Server : sort based on current date for last 6 months
SQL Server: sort values Into LIKE columns with dynamic pivot
Conversion rate in SQL Server (subquery)