2

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?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
mg1075
  • 17,985
  • 8
  • 59
  • 100

1 Answers1

2

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.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Bit of a follow-up: (1) is there a best practice to adhere to? (2) Are there scenarios where you have to or would want to refer to 'kitten' in your t-sql code, or is the only time it's important the time when you call sp_getapplock? – mg1075 Jul 30 '11 at 20:19
  • 1
    I don't know of any best practices regarding sp_getapplock. I do know that I've never seen it in production code. I'd imagine that anything that you can find about semaphores will apply here, though. As to the name of the lock that you use, I don't think it's not relevant anywhere outside of the sp_getapplock (and the associated sp_releaseapplock, of course). – Ben Thul Jul 31 '11 at 00:52
  • "...never seen it in production code". Good to know. I'm wondering because of Remus' response here: http://stackoverflow.com/questions/6880588/managing-synonyms-in-a-proc-with-nested-procs-using-sp-getapplock When I try to implement it, it's causing locks for me that seem to defeat the whole purpose of what I was hoping could be done. http://stackoverflow.com/questions/6853327/keep-table-downtime-to-a-minimum-by-renaming-old-table-then-filling-a-new-versio – mg1075 Jul 31 '11 at 00:57
  • 1
    applocks don't forgo the need to get locks that would otherwise be needed to accomplish what you're trying to do. That is, if you're trying to rename a table and that table is actively being queried, then the rename will wait until it's not being used. – Ben Thul Jul 31 '11 at 16:32