How to Clear a Record Lock in SQL

Record locking on a database is a normal and necessary procedure. When a transaction starts, the field being updated is locked so no other user can update the same field at the same time. The field will stay locked until the user who placed the lock either commits or does a rollback. The process to deal with locked records is different in Oracle, MySQL and SQL Server.

How to Detect and Clear a Locked Record in MySQL

MySQL automatically manages deadlocks and breaks them if necessary. As a Database Administrator (DBA), you can set the innodb_lock_wait_timeout to a smaller value if you need to reduce the time of row locks.

This parameter can be set with by typing the following SET SESSION statement:

SETinnodb_lock_wait_timeout = 10;

This can be done in MySQL Workbench in the query window.

Set Lock Timer (Image: Kathleen Estrada)

This can also be done from the command line.

MySQL Command Line (Image: Kathleen Estrada)

How to Detect and Clear a Locked Record in Oracle

In Oracle, to clear a record lock, you need to first find the session ID, or SID. To find the SID, in Oracle SQL Developer, type the following:

select session_id from dba_dml_locks where name = 'NAME';

Select Session ID (Image: Kathleen Estrada)

Next, find the serial number of the session using the following command:

select sid, serial# from v$session where sid in (select session_id from dba_dml_locks where name = 'NAME');

Select SID, Serial# (Image: Kathleen Estrada)

When you have the serial number and the SID, with DBA privileges, you can kill the session. The kill session command does not automatically terminate a user's session. It sends a command that asks the user's session to terminate safely. Database level kill commands will wait until a rollback is complete.

Tip

To terminate a session immediately, an administrator would need to kill a session from the operating system level.

alter system kill session 'SID, SERIAL#';

Kill Session (Image: Kathleen Estrada)

How to Detect and Clear a Locked Record in SQL Server

In SQL Server Management Studio, find the SID of the user creating the lock by running the following command:

EXEC SP_who 'active'

SP_who (Image: Kathleen Estrada)

After you locate the session ID that is causing the lock, you can kill that session.