Database lock can be used to restrict the access certain data to some particular user/session. The database locking is most commonly used to prevent updating same data by two or more different users. Database locking are usually done using ROLLBACK or COMMIT SQL statements. When a user/session is updating a data; it will be kept in locked state. The access to the data is restricted only to the user who is editing/updating the data. The state at which the data is kept while updating is called as lock wait state.
- Know: What is Deadlock in SQL
Why Database Locking is used?
Database locking is exercised to prevent potential data loss. Data losses are common when same updates in a database are concurrently done by two or more users. If same update is done several times it will lead to confusing and disastrous results. If database locks are used, it will prevent users from updating redundant data which in turn will protect the database.
Example for Database Locking
Consider that a user named “Trinity” is updating a data all the updating sessions expect “Trinity’s” will be kept under lock wait state. The lock wait on the other users will be released after the end “Trinity’s” update session. Databases like IBM and DB2 will return an error and release the lock wait; if an update session takes too much time. Lock wait period for Oracle databases are indefinite.
- Do you Know: SQL Clustered & Non-clustered Index
Types of Database Locking
Database lockings are classified based on the different lock granularity of the database. The different types of database locking are
- Database Level Locking
- File Level Locking
- Table Level Locking
- Page or Block Level Locking
- Column Level Locking
- Row Level Locking
Database Level Locking
It is possible to lock the entire database using the database level locking. When a database level lock is used only one user/session is allowed make changes in the database. The database level locking is not used unless the access restriction of all users is required. Database level locking is exercised whenever a major update is required to the database. Database level locking in Oracle database is known as exclusive mode.
File Level Locking
Using this locking method, a file present in a database can be locked. A file may consists of several tables and data types so unless an update is necessary for the whole file; this type of database locking is used seldomly.
Table Level Locking
Table level locking is used whenever a restriction for a particular table is required. This is the most commonly used database locking. Table locking is used whenever an update is required to a particular table. All table updates like altering rows, adding/removing columns and updating records are exercised under table level locking. Oracle database classifies table level locking as DDL (database definition lock). Create, drop and alter are the most commonly used statements in the DDL.
Page or Block Level Locking
This type of locking is exercised whenever a lock required for a page or block. Since the size of page and block are varied rapidly between the databases, these database locking are the least preferred.
Column Level Locking
Column level locking is used to to lock certain columns within a row of a database. Column level locking is not commonly used because it requires lot of resources to enable and disable the locks. Many database vendors offer very little support to column level locking.
Row Level Locking
Row level locking is used to restrict access to a particular in a table. All database vendors provide support for row level locking.
What is meant by Lock Contention?
Lock contention is a problem arises because of database locking. Lock contention is the process of competition between two users to update a database. Database locking will stall other sessions until the present is complete. This forces other sessions to wait for an indefinite period if one particular session is slower. Lock contention is a common problem in Oracle based databases.