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:
SET innodb_lock_wait_timeout = 10;
This can be done in MySQL Workbench in the query window.
This can also be done from the command line.
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';
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');
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.
To terminate a session immediately, an administrator would need to kill a session from the operating system level.
alter system kill session 'SID, SERIAL#';
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'
After you locate the session ID that is causing the lock, you can kill that session.
KILL <session ID>;