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

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)

Categories

HOME
dotnetrdf
sas
system-verilog
antivirus
agile
puppet
vlc
propertygrid
gspread
spring-cloud-contract
eval
google-plus
rebol2
cloudflare
scaling
scenebuilder
pycrypto
go-cd
azure-servicebus-queues
logarithm
katharsis
upnp
spring-data-neo4j
edge-detection
hana-studio
sharp
postgresql-9.2
graph-databases
m2e
mapguide
geomesa
socketscan
stdclass
google-account
constants
php-ews
ajp
lirc
w3-total-cache
timesten
range-v3
tuleap
nstouchbar
photon-controller
gce
addin-express
teiid
laravel-query-builder
gulp-typescript
sql-server-administration
glkit
try-finally
angularjs-ng-pattern
pax-web
shipitjs
clob
altbeacon
anythingslider
datainputstream
hyperthreading
hornetq
evo
response-headers
webhdfs
findcontrol
pgm
jazz
hiera
custom-url
low-level
chaining
cpan
kallithea
activity-streams
pgagent
alphablending
left-recursion
kendo-mvvm
omnifocus
dto
valence
dvcs
winrt-httpclient
osx-snow-leopard
driver-signing
leptonica
swrl
fortran77
zend-lucene
client-library
phpsh
lync-server-2010
returnurl
osi
static-variables
expression-evaluation
file-exists
msgbox
path-manipulation
noir
search-path
.app
exitstatus
writing

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