How to apply Row lock in SQL Server table

_{Saturday, December 23, 2017 3:39 PM}

Hi All,

How to apply row lock on tables, is that need to define from front end or any other source which try to insert or update data in table or do we have any setting in database to enable that option?

As per site, https://technet.microsoft.com/en-us/library/ms190615(v=sql.105).aspx

Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager. When an instance of the Database Engine processes a Transact-SQL statement, the Database Engine query processor determines which resources are to be accessed. The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. The query processor then requests the appropriate locks from the lock manager. The lock manager grants the locks if there are no conflicting locks held by other transactions.

So it means from application there is no locks mentioned the sql server engine applies locks based on query and internal mechanism , is that right understanding?

Thanks

_{Saturday, December 23, 2017 3:49 PM ✅Answered}

yes

Most cases it assumes the correct locking level based on ISOLATION LEVEL etc factors

However, in some rare case you may need to override it

This can be done by explicitly specifying the locking to be applied

like

SELECT ..
FROM table WITH (ROWLOCK)
...

here's another illustration

http://techo.luefher.com/databases/mssql-tsql/avoiding-deadlock-transaction-errors-by-using-rowlock-hint-in-t-sql/

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Saturday, December 23, 2017 3:54 PM ✅Answered}

Yes, the general principle is that the database engine implements the locking to uphold the semantics of isolation level chosen and you should not bother.

There are situations, you need to specify the locking level yourself. The most common case is when you read a value from a row that you plan to update later based on that value. Under the default isolation level READ COMMITTED, the lock on the row is released, and therefore the row could be modified before you come around to make your update. This does not happen with the isolation level REAPEATABLE READ, but with that isolation level, there is a different risk: if two processes read the row with the intention to update it later, they will deadlock.

For this reason, the row should be read with the hint UPDLOCK. A UPDLOCK does in itself not prevent other processes from reading the row, but only one process at a time can hold a UPDLOCK on a resource, so the one that comes in second will be blocked, so the first can why on with the update and there will be no deadlock.

_{Saturday, December 23, 2017 4:27 PM ✅Answered}

You normally get a row-lock for "free", you don't even have to ask for it, and as long as only a few rows need to be locked it stays as row-lock.  If you need to lock a bunch of rows, like "update mytable set hotlist=1 where lastname = 'Smith'" you may get page-locks instead, and in some cases it might even escalate to table-lock, all automatic.

Then there are possible complications if you define complex transactions with "begin transaction", but that's another topic.

Josh