February 16, 2012 at 2:24 pm
hi
how can we implement lock in sql server?
thanks
February 16, 2012 at 2:31 pm
What exactly are you asking? Are you trying to create a lock for some reason? Teaching? Demonstration? Testing?
Jared
CE - Microsoft
February 16, 2012 at 2:34 pm
i have this ques in interview,how can we implement lock in sql.what should i say?
February 16, 2012 at 2:43 pm
If you don't know the answer to this, you probably should not be interviewing for the job. Nevertheless, start here and learn about the different types of locks: http://msdn.microsoft.com/en-us/library/ms175519.aspx
Jared
CE - Microsoft
February 16, 2012 at 3:06 pm
Err, you don't. The database engine takes care of locking for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2012 at 3:09 pm
SQLKnowItAll (2/16/2012)
If you don't know the answer to this, you probably should not be interviewing for the job.
So tell me then, how do you (not the database engine) implement a lock in SQL Server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2012 at 3:12 pm
GilaMonster (2/16/2012)
SQLKnowItAll (2/16/2012)
If you don't know the answer to this, you probably should not be interviewing for the job.So tell me then, how do you (not the database engine) implement a lock in SQL Server?
I suppose I took the question to mean "what causes a lock?" or "what did I do to create a situation to cause a lock?"
Jared
CE - Microsoft
February 16, 2012 at 6:15 pm
i know different types of lock in sql ,but they ask me how to implement lock in sql
February 16, 2012 at 9:23 pm
GilaMonster (2/16/2012)
SQLKnowItAll (2/16/2012)
If you don't know the answer to this, you probably should not be interviewing for the job.So tell me then, how do you (not the database engine) implement a lock in SQL Server?
So do you select records from the database? I would say that based on your logic above the answer has to be no. The engine selects the records based on commands provided to the engine. But that would be a silly way to use language when a reasonable person would understand that indirect but inherent causality is still causality nonetheless. And by reasonable person I am referring to someone who would rather understand what others are saying without choosing to be overly technical for the sake of arguing.
I believe the point stands that if someone is in an interview for a position in which locking is relevant enough to ask about, posting a question such as the OP's would probably indicate they are interviewing for the wrong job. Not that I would respond in the same celko way, but I believe the substance of the response was valid.
February 16, 2012 at 10:03 pm
My guess would be they were asking for sp_getapplock (link).
February 16, 2012 at 11:08 pm
R.P.Rozema (2/16/2012)
My guess would be they were asking for sp_getapplock (link).
You beat me to it 🙂
February 17, 2012 at 5:58 am
SQL Kiwi (2/16/2012)
R.P.Rozema (2/16/2012)
My guess would be they were asking for sp_getapplock (link).You beat me to it 🙂
Ok, this is a new one for me. Why would one use this? Second, why is this an interview question? Third, do these people get these questions before the interview to study up on? I really don't understand all of these types of posts origins, or the "list of interview questions" that get posted places. Mind you, I have only been on 3 interviews for a DBA position, but not one of this "deep" into the realm of SQL Server.
Jared
CE - Microsoft
February 17, 2012 at 6:38 am
SQLKnowItAll (2/17/2012)
Ok, this is a new one for me. Why would one use this?
Application locks can be handy in all sorts of situations. One example that I use currently is where I have a procedure that contains a critical section - code that is only safe to run by one process at a time.
Second, why is this an interview question?
Why not? It's a reasonable question to ask a SQL Server developer I would say.
Third, do these people get these questions before the interview to study up on? I really don't understand all of these types of posts origins, or the "list of interview questions" that get posted places. Mind you, I have only been on 3 interviews for a DBA position, but not one of this "deep" into the realm of SQL Server.
Who can say? My guess is the question was asked, the interviewee had no answer, so is asking us to help him/her learn for next time.
February 17, 2012 at 7:03 am
SQL Kiwi (2/17/2012)
SQLKnowItAll (2/17/2012)
Ok, this is a new one for me. Why would one use this?Application locks can be handy in all sorts of situations. One example that I use currently is where I have a procedure that contains a critical section - code that is only safe to run by one process at a time.
Second, why is this an interview question?
Why not? It's a reasonable question to ask a SQL Server developer I would say.
Third, do these people get these questions before the interview to study up on? I really don't understand all of these types of posts origins, or the "list of interview questions" that get posted places. Mind you, I have only been on 3 interviews for a DBA position, but not one of this "deep" into the realm of SQL Server.
Who can say? My guess is the question was asked, the interviewee had no answer, so is asking us to help him/her learn for next time.
As always... Well said Paul. Thanks for the input. Not being a developer, I suppose I did not realize the practical application of this. Good to know!
Jared
CE - Microsoft
February 17, 2012 at 7:12 am
Hi,
You can implement locks in SQL Server using locking hints read below:
Hints
There are times when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation levels. The available hints include the following:
FASTFIRSTROW—The query is optimized to get the first row of the result set.
HOLDLOCK—Hold a shared lock until the transaction has been completed.
NOLOCK—Do not issue shared locks or recognize exclusive locks. This may result in data being returned that has been rolled back or has not been committed; therefore, working with dirty data is possible. This may only be used with the SELECT statement.
PAGLOCK—Locks the table.
READCOMMITTED—Read only data from transactions that have been committed. This is SQL Server's default behavior.
READPAST—Rows locked by other processes are skipped, so the returned data may be missing rows. This may only be used with the SELECT statement.
READUNCOMMITTED—Equivalent to NOLOCK.
REPEATABLEREAD—Locks are placed on all data used in queries. This prevents other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.
ROWLOCK—Locks the data at row level. SQL Server often locks at the page or table level to modify a row, so developers often override this setting when working with single rows.
SERIALIZABLE—Equivalent to HOLDLOCK.
TABLOCK—Lock at the table level. You may want to use this when performing many operations on table-level data.
UPDLOCK—Use update locks instead of shared locks while reading a table, and hold locks until the end of the transaction. This has the advantage of allowing you to read data without locking and to update that data later knowing the data has not changed.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comViewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply