What is a database lock in the context of SQL? Provide an example and explanation.
A database lock is used to “lock” some data in a database so that only one database user/session may update that particular data. So, database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time. When data is locked, then that means that another database session can NOT update that data until the lock is released (which unlocks the data and allows other database users to update that data. Locks are usually released by either a ROLLBACK or COMMIT SQL statement.
What happens when another session tries to update the locked data?
Suppose database session A tries to update some data that is already locked by database session B. What happens to session A? Well, session A will actually be placed in what’s called a lock wait state, and session A will be stopped from making further progress with any SQL transaction that it’s performing. Another way of saying this is that session A will be “stalled” until session B releases the lock on that data.
If a session ends up waiting too long for some locked data, then some databases, like DB2 from IBM, will actually time out after a certain amount of time and return an error instead of waiting and then updating the data as requested. But some databases, like Oracle, may handle the situation differently – Oracle can actually leave a session in a lock wait state for an indefinite amount of time. So, there are a lot of differences between different database vendors in terms of how they choose to deal with locks and other sessions waiting for locks to be released.
Database locking techniques
Database locks can actually be placed at different levels – also known as lock granularity – within the database.
Here is a list of the usual lock levels and types supported, and more information on what each technique means:
Database level locking
With database level locks, the entire database is locked – which means that only one database session can apply any updates to the database. This type of lock is not often used, because it obviously prevents all users except one from updating anything in the database. But, this lock can be useful when some major support update is necessary – like upgrading the database to a new version of the software. Oracle actually has an exclusive mode, which is used to allow just one user session use the database – this is basically a database lock.
File level locking
With a file lock level, an entire database file is locked. What exactly is a file in a database? Well, a file can have a wide variety of data – inside a file there could be an entire table, a part of a table, or even parts of different tables. Because of the variety of data stored inside a file, this type of lock level is less favored.
Table level locking
A table level lock is pretty straight forward – it means that an entire table is locked as a whole. This lock level comes in handy when making a change that affects an entire table, like updating all the rows in a table, or modifiying the table to add or remove columns. In Oracle, this is known as a DDL lock, because it’s used with DDL (Data Definition Language) statements like CREATE, ALTER, and DROP – basically statements that modify the entire table somehow or the other.
Page or block level locking
Block, or page, level locking occurs when a block or page that is part of a database file is locked. To read more about pages and blocks if you are not already familiar with them, then go here: Pages versus blocks.
Because the data that can be stored in blocks/pages can be wide and varied, page/block locking is less favored in databases today.
Column level locking
A column level lock just means that some columns within a given row in a given table are locked. This form of locking is not commonly used because it requires a lot of resources to enable and release locks at this level. Also, there is very little support for column level locking in most database vendors.
Row level locking
A row level lock applies to a row in a table. This is also the most commonly locking level, and practically all major database vendors support row level locks.
Are locks automatically used by databases?
When data is either deleted or updated locks are always used – even if a database user doesn’t write his/her SQL to explicitly say that a lock must be used. Many of the RDBMS’s out there today also have support to use the “FOR UPDATE OF” clause combined with a normal SELECT statement. The FOR UPDATE OF clause basically says that the database user intends to update some data – although the database user is not required to make changes to that particular data either. And, because the intent of updating data is declared, it means that a lock will be placed on that data as well.
Example of database locking
As a simple example of when locking would be used by database, suppose we have the following SQL:
UPDATE some_table SET some_field = "some_value" WHERE some_column = "XYZ";
The SQL statement above will lock the row or rows which have a value of “XYZ” for the column named “some_column”. The locking of the row(s) happens behind the scenes as part of the RDBMS software, and it prevents other database user sessions from updating the same row(s) at the same exact time as well.
Can data be read when a lock is in place?
It depends on the lock, since some locks are read-exclusive, which means that other sessions in the database can not even read the locked data – so if .
What is the point of database locking?
If it’s not already clear to you, the reason we have database locks is to prevent the potential loss of data that could happen if updates are applied concurrently – or at the same exact time. If two different database users are allowed to update the same data at the same exact time, then the results could be potentially confusing and disastrous. But if that same data were locked, then that issue would not arise, since only one user could update the locked data at a time.
What is lock contention?
One problem that occurs with having locks is that locks can cause what’s known as contention, which means that because there are locks on the data, sessions that exist at the same time (concurrent sessions) are essentially competing for the right to apply updates on the same data, because that data may be locked by any given session. In the best case, lock contention means that some user processes run slower because a session is waiting for a lock. In the worst case, having sessions compete for locks can make sessions stall for an indefinite period of time.
When sessions do stall for an indefinite period of time, that is known as deadlock, which you can read more about here: Database deadlock.
You should also read about the concept of Lock Escalation, which is a built in feature of many RDBMS’s today.