CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
mg1075
mg1075

Reputation: 18155

What is @Resource supposed to refer to when using sp_getapplock?

The MSDN documentation for sp_getapplock says:

[ @Resource= ] 'resource_name' Is a string specifying a name that identifies the lock resource.

The lock resource created by sp_getapplock is created in the current database for the session. Each lock resource is identified by the combined values of:

  • The database ID of the database containing the lock resource.
  • The database principle specified in the @DbPrincipal parameter.
  • The lock name specified in the @Resource parameter.

My questions:
1. is the 'resource_name' just any old name you make up?
2. does the 'resource_name' have to refer to a table name or stored proc name or a (named) transaction name?

Upvotes: 2

Views: 2180

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

Yes, it's any old name you make up. You can say "sp_getapplock 'kitten'" and it will wait for the "kitten" lock to be released before acquiring it for itself and continuing on. You have to define the resources that make sense to serialize the access to.

I don't like the idea of naming the lock after a table because then it implies to other coders that access to that table is serialized when there's nothing in SQL Server (except for the applock framework) to enforce that. Put another way, applocks are sort of like a traffic light. There's nothing inherent about a red light that prevents you from going forward. It's just a good idea not to.

Upvotes: 2

Related Questions