sql-server


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.

Related Links

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

Categories

HOME
crystal-reports
vue.js
project-management
puzzle
steam
apache2
wampserver
apple-numbers
jrebel
triggers
constructor
ndis
rfid
lagom
cs-cart
event-log
google-openid
internet-explorer-8
prestodb
ios10.3
multicore
koa
shippo
uiautomator
countif
header-files
printdocument
interrupt-handling
web-frontend
nesc
multichoiceitems
gzip
dd-wrt
pygooglechart
ioc-container
preg-grep
freebase
spreedly
ios-ui-automation
passenger
mms
android-download-manager
coreclr
rule
web-development-server
bayesian-networks
mediawiki-extensions
control-flow-graph
rkt
facebook-chatbot
windows-firewall
workflow-foundation-4.5
android-bitmap
iphone-developer-program
festival
efxclipse
verbose
nsfetchedresultscontrolle
typhoon
windows-azure-pack
icefaces
chessboard.js
dts
uiprogressview
fortran90
magento-1.4
optionbutton
ubercart
xaml-designer
adodb
jython-2.7
multipleselection
ejabberd-saas
line-numbers
docopt
mov
aquafold
argument-passing
kraken.js
mbox
facebook-chat
cling
awesomeprint
fieldset
sametime
git-filter-branch
qsqltablemodel
pstree
getstring
responsetext
nssavepanel
charts4j
port-scanning
communicationexception
aio
oggvorbis
outlook-form

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App