February 17, 2012 at 7:25 am
Basit Farooq (2/17/2012)
You can implement locks in SQL Server using locking hints read below:
Much of that 'information' is wrong. Might have been easier/better to have simply linked to BOL topics instead?
February 17, 2012 at 7:51 am
This is a question I ask during the Interview.
Setting the "serialzation" or "Transaction Level Isolation" are the most common wrong answers.
The right answers are all over this MSDN document.
http://msdn.microsoft.com/en-us/library/ms189857.aspx
For me it is a great question to understand if the person is comfortable and experianced with advanced Transact SQL development.
If they squirm while answering this question, it is time to ask about the differenc in using a WITH for Hints and one for Common Table Expressions. 😎
BTW - If anyone know anybody in the Nashville area that would like a DBA/SQL Development Job, send me a private message. The company pays well and has awesome benefits.
February 17, 2012 at 8:19 am
SanDroid (2/17/2012)
This is a question I ask during the Interview.Setting the "serialzation" or "Transaction Level Isolation" are the most common wrong answers.
The right answers are all over this MSDN document.
http://msdn.microsoft.com/en-us/library/ms189857.aspx
For me it is a great question to understand if the person is comfortable and experianced with advanced Transact SQL development.
If they squirm while answering this question, it is time to ask about the differenc in using a WITH for Hints and one for Common Table Expressions. 😎
BTW - If anyone know anybody in the Nashville area that would like a DBA/SQL Development Job, send me a private message. The company pays well and has awesome benefits.
The link you gave just talks abut hints. Not how actually to explicitly create a lock. I know what locks are, how the engine creates them, how it deals with them, and how to troubleshoot them when they are frequent and unnecessary. However, my guess is that very few developers are familiar with the stored proc mentioned. Mainly because I "assume" (maybe incorrectly) that it is not a frequently necessary SP.
Jared
CE - Microsoft
February 17, 2012 at 12:31 pm
@paul-2 I reviewed MSDN's explanation of the sp here. It didn't really give a good application for this, as it sometimes does. Could you give me a case scenario as to why you use this? I know you gave me a "scenario," but a concrete example would help me a bit to visualize why this is needed in that section versus what the engine will already do.
Jared
CE - Microsoft
February 17, 2012 at 1:53 pm
I've used it in multiple scenarios. One is a sql server agent job that I need to be run once and only once at a time. If it is run twice things will be messed up. I could go and create a table and put a table lock on that, or something similar, so that any 2nd instance will run into the lock and can not continue for as long as the 1st instance is still running (i.e. keeping the lock) but why would I create a table just to create a semaphore?.
So what I did in the stored procedure is to start a transaction and call sp_getapplock, asking for an exclusive lock on some string identifying my process, say "MyProcessNameHere". So on the call to sp_getapplock I specify @Resource = 'MyProcessNameHere', @LockMode = 'Exclusive', @LockOwner = 'Transaction' and @lockTimeout = 0. I get the sp's return value and check for it to be 0 or 1. Actually 1 will not occur because I specified @LockTime = 0, but to be complete both 0 and 1 mean we got the lock. If it isn't 0 or 1, some other instance must be running and I exit the procedure by raising an error. Only the first instance will get the lock and keep it for as long as it inside it's transaction, so I can be 100% sure only one instance is running at any time.
On another application I used it to temporary suspend operations when I need some sort of a maintenance job to be run. Inside a set of triggers on the tables that I need to be untouched while the job is running, the very first thing I do is to call sp_getapplock with @LockMode = 'Shared', again on some application specific resource string. Now if the maintenance job is run, this calls sp_applock with @LockMode = 'Exclusive' on the same resource string. The maintenance job will only get it's exclusive lock once all triggers have released their shared locks and once the maintenance job is running any triggers that do get fired will be suspended waiting upon the maintenance job's lock to be released.
February 17, 2012 at 9:04 pm
SQLKnowItAll (2/17/2012)
@paul I reviewed MSDN's explanation of the sp here. It didn't really give a good application for this, as it sometimes does. Could you give me a case scenario as to why you use this? I know you gave me a "scenario," but a concrete example would help me a bit to visualize why this is needed in that section versus what the engine will already do.
To add to R.P.Rozema's examples, have a look at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx
I can't give the details of the solution I referred to earlier because I am bound by a confidentiality agreement; but I can say that application locks are useful to solve a broad class of problems that require a 'mutex'. The biggest benefit, for me, is their simplicity. In principle, complex cases or high-concurrency scenarios can be resolved with very careful coding and regular locking hints, but this is often not a practical proposition.
February 18, 2012 at 12:23 pm
Thanks Paul and Rozema!
Jared
CE - Microsoft
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply