Why we lock and unlock record in SQL Server Database? And how do we lock and unlock records as we will?
1. Lock Record in SQL Server Database
“I 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?”
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.
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.
insert a row into table4
while
begin
begin transaction
select * from table4 WITH(ROWLOCK, HOLDLOCK) —- locking only the new record
update table1
update table2
update table3
update table4
commit transaction
fetch next
end
2. How to Unlock Locked Row in SQL Server Database
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.
Now take Transaction – SQL to unlock record in SQL Server database.
UNLOCK [RECORD nRecordNumber] [IN nWorkArea | cTableAlias] [ALL]
RECORD nRecordNumber: Releases the record lock on record number nRecordNumber.
IN nWorkArea | cTableAlias:
Releases a record lock (or locks) or a file lock from a table in a specific work area. nWorkArea specifies a work area number and cTableAlias specifies a table alias. If you don’t include nWorkArea or cTableAlias, UNLOCK releases a record lock (or locks) or a file lock from the table in the currently selected work area.
Related Articles: