{"id":1674,"date":"2014-06-25T09:44:47","date_gmt":"2014-06-25T09:44:47","guid":{"rendered":"http:\/\/www.isunshare.com\/blog\/?p=1674"},"modified":"2023-04-17T03:27:47","modified_gmt":"2023-04-17T03:27:47","slug":"lock-and-unlock-record-in-sql-server-database","status":"publish","type":"post","link":"https:\/\/www.isunshare.com\/blog\/lock-and-unlock-record-in-sql-server-database\/","title":{"rendered":"Lock and Unlock Record in SQL Server Database"},"content":{"rendered":"

Why we lock and unlock record in SQL Server Database<\/strong><\/a>? And how do we lock and unlock records as we will?<\/p>\n

1. Lock Record in SQL Server Database<\/h2>\n

\u201cI want to insert a record into permanent table but I don’t want anyone (any other session) to have access to that record until I allow it. I cannot use transaction for this case. Will sp_getapplock work? If yes, how to code this?\u201d<\/p>\n

This is one kind situation when we wish to lock record in SQL Server tables. And it is the most important reason to lock record.<\/p>\n

Now we suppose there are four tables in SQL Server database. And we are ready to add row into table 4, so we would not like any influence when adding row. The following code is used to lock table4 and record.<\/p>\n

insert a row into table4<\/em>
\n while<\/em>
\n begin<\/em>
\n begin transaction<\/em>
\n select * from table4 WITH(ROWLOCK, HOLDLOCK) —- locking only the new record<\/em>
\n update table1<\/em>
\n update table2<\/em>
\n update table3<\/em>
\n update table4 <\/em>
\n commit transaction<\/em>
\n fetch next<\/em>
\n end<\/em><\/p>\n

2. How to Unlock Locked Row in SQL Server Database<\/h2>\n

There are several commands on the tables in SQL Server database. If an application times out or something goes wrong on the server, a lock would be mad on a table, causing problems for other users when they are trying to access records on the tables.<\/p>\n

Now take Transaction – SQL to unlock record in SQL Server database<\/strong>.<\/p>\n

UNLOCK [RECORD nRecordNumber]\u00a0 [IN nWorkArea | cTableAlias]\u00a0\u00a0 [ALL]<\/strong><\/p>\n

RECORD nRecordNumber<\/em>:<\/strong> Releases the record lock on record number nRecordNumber<\/em>.<\/p>\n

IN nWorkArea<\/em> | cTableAlias<\/em>:<\/strong><\/p>\n

Releases a record lock (or locks) or a file lock from a table in a specific work area. nWorkArea <\/em>specifies a work area number and cTableAlias<\/em> specifies a table alias. If you don’t include nWorkArea<\/em> or cTableAlias<\/em>, UNLOCK releases a record lock (or locks) or a file lock from the table in the currently selected work area.<\/p>\n

Related Articles:<\/strong><\/p>\n