Database Locking

Environments

All Databases

Issue

A database lock occurs when a query or transaction is blocked or stalled as a result of a lock conflict. This can be seen in code execution or log messages such as sqlite3.OperationalError: database is locked.

Root Cause

Database locks can happen for a number of reasons. Some of the most common causes of database locks are:

• Concurrent access to the same data: If multiple processes attempt to access the same data at the same time, the database system may need to use locks to ensure that the data is not modified by one process while being read by another.
• Long-running transactions: If a transaction takes a long time to complete, it may hold locks on the data it is accessing for an extended period of time, preventing other processes from accessing it.
• Multiple processes competing for limited resources, such as disk or memory, may necessitate the use of locks by the database system to ensure that the resources are used fairly.
• Deadlocks occur when two or more processes wait for one another to release locks that they are holding. This can lead to a never-ending cycle of waiting, resulting in a deadlock.
• Hardware or software failures can sometimes cause locks to be held for an extended period of time, even after a failure has been resolved.

Resolution

In various database systems, the following error messages may indicate a database lock:

• MySQL:

"Deadlock discovered while attempting to obtain lock"
"Lock wait timeout has been exceeded."

You can try the following to resolve a database lock in MySQL:

• Using the KILL command, terminate the process that is holding the lock.
  If the lock is caused by an open transaction, you can use the ROLLBACK command to roll back the transaction.
• If the lock is caused by a table that is being used by another process, you can identify it with the SHOW PROCESSLIST command and then kill it with the KILL command.

• SQL Server:

"Transaction (Process ID) was deadlocked on lock resources with another process and was chosen as the victim of the deadlock. Restart the transaction."

You can try the following to resolve a database lock in SQL Server:

• If the lock is caused by an open transaction, you can use the ROLLBACK command to roll back the transaction.
• If the lock is caused by a table that is being used by another process, you can identify it with the sp_who2 stored procedure and then kill it with the KILL command.

• SQLite:

"The database has been locked."

To resolve a SQLite database lock, you can try the following:

• Using the dbDisconnect function, you can disconnect from the database.
  If you have an open transaction that is causing the lock, you can commit it with the dbCommit function

• MongoDB

"Error: couldn't execute operation: couldn't execute command."

"CommandFailed: Transaction 12 is being rolled back - lock was not available"

You can try the following to resolve a database lock in MongoDB

• If the lock is being held by a long-running operation, you may have to wait for it to finish before releasing the lock.
• If the operation that is holding it is taking too long or is stuck, you can terminate it with the db.killOp() method.
• If the lock is caused by concurrent transactions, you can try using a higher isolation level to ensure that your transactions do not conflict with one another.
• Restarting the MongoDB server (as a last resort) may help resolve the lock if it is caused by a bug or a system issue.

Linux tooling

Outside of the database(s) themselves, you could use the following linux tools to identify potential signs of locking:

ps: This command displays the currently running processes on a system, as well as information about which processes are holding locks.

lsof: This command displays a list of all open files on a system, including those that are locked. This command can be used to determine which processes have locks on which files.

strace: This command allows you to trace a process's system calls and signals. This command can be used to determine which system calls are being made by a process that is holding a lock and, potentially, the root cause of the lock.

fuser: This command tells you which processes are using which files or file systems. This command can be used to determine which processes have locks on a specific file or file system.

Notes

It is important to note that database locks can degrade performance and should be avoided whenever possible. To reduce the need for locks, try designing your schema and queries to minimise contention, and you can also experiment with different isolation levels to ensure that your transactions do not conflict with one another.