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.
Performance Issues with Count(*) in SQL Server
Replication advice for local SQL Server to hosted server
How do I select columns together with aggregate functions?
SQL Server / .NET - Get the connection pool size
Amazon RDS SQL Server Change Database Owner
Convert Access query into SQL Server stored procedure
How to Add Entity Without Recreating Tables
SQL Server TOP constraint [closed]
How to convert 71632.0638353154 to 71,632.06 in SQL Server and SYBASE?
I want to fetch autoincremented varchar values from SQL Server 2008
Copy and paste excel to sql server table
C#.net Console Application SQLCommand.CommandTimeOut property not working
Is there a limit to the number of Common Table Expression (CTE) expressions allowed?
Lightswitch : can it create a real time SQLServer DB Monitoring application?
Most Efficient Many-To-Many Linq Query
Repeated Name/Password Requests Using MS Access 2003 and SQL Server 2008