What is database locking in the context of SQL?
Database locking is used to lock out some data in the database so that only one database user / session can update that specific data. Therefore, database locking exists to prevent two or more databse users from updating the exact same data at the exact same time. When the data is locked, it means that another database session CANNOT update that data until the lock is released (unlocking the data and allowing the other database user to update that data. by the ROLLBACK or COMMIT statement in SQL.
What happens when another session tries to update locked data?
Suppose database session A tries to update some data that has been locked by database session B. What happens to session A? Session A will actually be placed in a state called lock wait, and session A will stop processing for any SQL transactions that are executing. In other words, session A will be delayed until session B releases the lock on that data.
If a session waits too long for some data to be locked, some databases will be timeout after a certain amount of time and return an error instead of waiting and then updating the data as required. bridge. But some databases, like Oracle, can handle different situations – Oracle can leave the session in lock pending state for an indefinite period of time. Therefore, there are many differences between different database providers on how they choose to handle the key. and other sessions are waiting for the lock to be released.
Data locking skills
Database locks can actually be done at many different levels – also known as granularity locks – inside the database.
Here is a list of the lock levels and supported data types.
Database level locking
With database-level locking, 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 commonly used, because it clearly prevents all users except one from updating anything in the database. However, this key may be useful when some major support updates are needed – such as upgrading the database to a new version of software. The Oracle database actually has an exclusive mode, which is used to allow only one session database user – basically, this is a database key.
File level locking
With the file level lock, an entire database file is locked. So what exactly is a file in the database? It can be a wide range of data – inside a file can be a whole table, part of the table, or parts of different tables. Because of the variety of data stored in a file, this level of locking is not used by many people.
Table level locking
The table-level lock is fairly simple – meaning the whole table is locked completely. This key level is useful when making changes that affect the entire table, such as updating all rows in the table or adjusting the table. In Oracle, this is called the DDL key, because it is used with statements. DDL (Data Definition Language) like CREATE, ALTER and DROP – basically statements that modify the entire table in one way or another.
Page or block level locking
Page or block 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 new to them, visit here: Pages versus blocks.
Because data can be stored in blocks / pages can be wide and varied, page keys / blocks are less popular in today’s databases.
Column level locking
A column-level lock means that some columns in a given row in a given table are locked. This type of lock is not commonly used because it requires a lot of resources to activate and release locks at this level. In addition, there is little support for column-level locking in most database vendors.
Row level locking
The row level lock applies to a row in the table. This is also the most common key level and practically all major database providers support row-level keys.
Is locking automatically used by the database?
When data is deleted or updating locking is always used – even if the database user doesn’t write his SQL to make it clear that the lock must be used. Many RDBMS now also support to use the “FOR UPDATE OF” command in conjunction with a normal SELECT statement. Basically, the “FOR UPDATE OF” clause states that database users intend to update some data – although database users are not required to change that specific data. Updated data is declared, which means that the lock will also be performed on that data.
Database locking example
As a simple example of locking that will be used by the database, suppose we have the following SQL:
1 2 3 | UPDATE some_table SET some_field = "some_value" WHERE some_column = "XYZ"; |
The above SQL statement locks one or more rows with the value of “XYZ” for the column named “some_column” in use. Locking the row (s) implicitly occurs as part of the RDBMS software and it also prevents other database user sessions from updating the same row at the exact same time.
Can data be read when locked?
It depends on the lock, because some locks are read-exclusive, which means that other sessions in the database cannot even read the locked data.
So what is the purpose of database locking?
If you’re not sure, the reason we have a database lock is to prevent possible data loss if updates are applied simultaneously. If two different database users are allowed to update the same data. At the exact same time, the results can be confusing and disastrous. But if the same data is locked, that problem won’t arise, because only one user can update the locked data at a time.
What is a locked dispute?
One problem with locking is that a lock can cause a dispute, which means that because there is a lock on data, the sessions that exist at the same time (concurrent sessions) are essentially competing to gain control. Use updates on the same data, because that data can be locked by any session. In the best case, a lock dispute means that some user processes run slower because the session is waiting for a lock. In the worst case, competing sessions to lock out can create stagnation for multiple sessions for an indefinite period of time.
When sessions are delayed for an indefinite period of time, called a deadlock, you can read more about this: database deadlock
Refer
https://www.programmerinterview.com/database-sql/database-locking/