Introduction to database lock concepts

A database is a shared resource used by multiple users, just like an operating system. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency. Locking is a very important technology for implementing database and sending control. In practice, we often encounter lock-related exceptions. When two transactions need a set of conflicting locks and cannot continue the transaction, a deadlock will occur, strict impact on normal application execution.
There are two basic lock types in the database: exclusive locks and share locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.
First case of deadlock
One user a accesses Table A (Table A is locked) and then table B; the other user B accesses table B (Table B is locked), and then tries to access Table; because user B has locked table B, user a must wait for user B to release Table B. User B must wait for user a to release Table A to continue, this causes deadlocks.
Solution:
This deadlock is common because of bugs in the program. There are no other methods except for the logic of the adjusted program. Carefully analyze the program logic. When performing multi-Table operations on the database, try to process them in the same order and avoid locking two resources at the same time. For example, when operating two tables A and B, it is always processed in the order of A and B. When two resources must be locked at the same time, make sure that the resources should be locked in the same order at any time.
The second situation of deadlock
User A queries a record and then modifies the record. Then user B modifies the record. In this case, the nature of the lock in user a's transaction is increased from the shared lock in the query to the exclusive lock, because A has a shared lock, the exclusive lock in user B must wait for a to release the shared lock, and the exclusive lock that a cannot rise due to the exclusive lock of B cannot be released, A deadlock occurs. This type of deadlock is relatively hidden, but it often occurs in the larger metrics. For example, if a button on a project does not expire immediately after it is clicked, the user can quickly click the same button multiple times, in this way, the same piece of code performs multiple operations on the same record in the database, which is easy to cause this deadlock.
Solution:
1. For buttons and other controls, click them to make them invalid immediately, so that users are not allowed to click them again to avoid operations on the same record at the same time.
2. Use optimistic locks for control. Optimistic locks are mostly implemented based on the data version record mechanism. Add a version ID for the data. In the database table-based version solution, you can add a "version" field to the database table. When reading the data, read the version number together, and then add one to the version number when updating the data. In this case, the version data of the submitted data is compared with the current version information recorded in the corresponding data base table. If the submitted data version number is greater than the current version number of the database table, it is updated, otherwise, expired data is considered. The Optimistic Locking mechanism avoids the overhead of locking the database in long transactions (neither user a nor user B locks the database data during operations ), this greatly improves the overall performance of the system with high concurrency. Hibernate
The optimistic lock implementation is built in its data access engine. Note that the optimistic lock mechanism is implemented in our system, and the user update operations from external systems are not controlled by our system, therefore, dirty data may be updated to the database.
3. Use pessimistic locks for control. Pessimistic locks are implemented by the database lock mechanism in most cases, such as the Oracle select... For update statement to ensure the maximum degree of exclusiveness of the operation. However, there is a large amount of database performance overhead, especially for long transactions, which is often unbearable. For example, in a financial system, when an operator reads user data and modifies it based on the read user data (such as changing the user account balance), if a pessimistic lock mechanism is used, this means that the database records are always locked during the entire operation (from the operator's reading data, starting modification to submitting modification results, or even the time when the operator goes to coffee making, it can be imagined that if there are hundreds of concurrent jobs
The case may have disastrous consequences. Therefore, we must consider the use of pessimistic locks for control.
The third situation of deadlock
If an update statement that does not meet the conditions is executed in the transaction, a full table scan is executed to promote the row-Level Lock to the table-Level Lock. After multiple such transactions are executed, it is easy to cause deadlocks and congestion. Similarly, when the data volume in the table is very large and the index is too small or inappropriate, full table scanning often occurs, and the application system will become slower and slower, eventually congestion or deadlock occurs.
Solution:
Do not use too complex queries associated with multiple tables in SQL statements. Use the "Execution Plan" to analyze SQL statements and create indexes for SQL statements with full table scans for optimization.
5. Summary
Generally, memory overflow and lock tables are caused by poor code writing. Therefore, improving the code quality is the most fundamental solution. Some people think that this idea is wrong to first implement the function and correct it in the test phase when there is a bug. If the quality of a product is determined in the manufacturing process, rather than in the quality inspection, the quality of the software has been determined in the design and coding stage, testing is only a verification of software quality, because it is impossible to identify all the bugs in the software.

Related Articles:
Several lock modes in Oracle
Encountered a problem of multi-transaction concurrency
MySQL database lock

Recommendation circle: pipboy
For more information about the lock mechanism, two conditions are required:
1. Large data volume
2. Concurrent Operation by multiple users
Without these two conditions, the database is not prone to deadlock. It may take half the effort for research. If both conditions are met, but you still process the data according to the default settings of the database, there will be many problems, such:
1) lost updates
A and B read and modify the same data. The Modification result of one user destroys the Modification result of the other user.
2) Dirty read
When user a modifies the data, user B also reads the data, but user a cancels the modification to the data for some reason and restores the original data value, the data obtained by B is inconsistent with the data in the database.
3) Non-repeated read
User B reads the data and modifies it. At the same time, user A is also reading the data. At this time, user a reads the data again and finds that the values of the two operations are inconsistent.
As a multi-user database system, SQL server uses locks to implement Concurrency Control in units of transactions. Sqlserver uses a "Lock" to ensure transaction integrity and data consistency.

I. Lock Concept
Locking is the most common concurrency control mechanism. It is a major means to prevent other transactions from accessing specified resource control and achieve concurrency control. A lock is before a transaction accesses a database's resources (such as tables and records), it first requests the system to block the resource. After the transaction acquires the lock, it obtains control over the data, before a transaction releases its lock, other transactions cannot update this data. When the transaction is canceled, the locked resources are released.
When a user locks an object in the database, other users cannot access the object.

Ii. Lock Granularity
SQL Server 2000 has multi-granularity locking, allowing a transaction to lock different types of resources. To minimize the cost of locking, SQL Server automatically locks resources at the appropriate task level. Locking at a small granularity (such as rows) can increase concurrency but requires a large overhead, because if many rows are locked, more locks need to be controlled. Locking in a large granularity (such as a table) is expensive in terms of concurrency, because locking the entire table limits access to any part of the table by other transactions, but requires a low overhead, because the number of locks to be maintained is small. SQL Server can lock rows, pages, extended disk areas, tables, databases, and other resources.

Resource level description
Single Row in the RID row lock table
Rows in the key row-Level Lock Index
Page locks a data page or index page
Extent page-Level Lock a set of data pages or index pages
Table-level lock for the entire table
Database database locks the entire database

The granularity depends on the data operation. If all rows in the table are updated, table-level locks are used. If a row in the table is updated, row-level locks are used.
Row-level locks are the optimal locks, because row-level locks cannot be wasted because data is occupied and not used. However, if you frequently operate on multiple records in a table in transactions, row-level locks will be applied to many recorded rows in the table, the number of locks in the database system increases sharply, which increases the system load and affects system performance. Therefore, SQL Server also supports lock escalation ).
The so-called lock upgrade refers to adjusting the lock granularity and replacing multiple low-granularity locks with a few higher-granularity locks to reduce the system load. In SQL Server, when there are many locks in a transaction and the lock upgrade threshold is reached, the system automatically upgrades row-level locks and page locks to table-level locks.
In SQL Server, the threshold for Lock upgrade and lock upgrade are automatically determined by the system and do not need to be set by the user.

Iii. Lock mode
Lock mode and description table

Lock mode description
Share (s) is used without changing or updating data (read-only operation), such as select statement
Update (u) is used in updatable resources. It prevents common deadlocks when multiple sessions are reading, locking, and subsequent resource updates.
Arrange it (x) for data modification operations, such as insert, update, or delete. Ensure that multiple updates to the same resource are not performed at the same time
Intention when the Microsoft SQL Server database engine acquires a low-level lock, it will also place the intention lock on the object containing lower-level objects. for example, when the row or index key range is locked, the database engine places the intention lock on the page that contains the row or key. When a page is locked, the database engine places an intent lock on a higher-level object that contains the page.
Intention lock type: Intention sharing (is), intention ranking (IX), and intention ranking sharing (six)
The architecture is used to perform operations dependent on the table architecture. The architecture lock types are: schema modification (Sch-m) and schema stability (Sch-S)
Large-capacity Update (BU) is used to copy data to a table in large capacity and specify the tablock prompt.

Iv. SQL Server lock settings
1. Handle deadlocks and set deadlock priority
A deadlock occurs when multiple users apply for different blockages. The applicant has part of the blockages and waits for the blockages of other users.
Set deadlock_priority can be used to control the session response mode when a deadlock occurs.
Syntax:
Set deadlock_priority {LOW | normal}
Here, low indicates that the session priority of the process is low. In the case of a deadlock, the transaction of the process can be interrupted first.
2. Process timeout and set the lock timeout duration.
@ Lock_timeout returns the current lock timeout settings for the current session, in milliseconds
Set lock_timeout allows the application to set the maximum time for the statement to wait for resource blocking. When the waiting time of a statement is greater than the lock_timeout setting, the system automatically cancels the blocking statement and Returns Error 1222 to the application that "exceeds the lock request timeout period ".
Example
1) set the lock timeout period to 1,800 milliseconds.
Set lock_timeout 1800
2) configure the index lock Granularity
You can use the sp_indexoption system stored procedure to set the locking granularity for indexes.
3) set the transaction isolation level
SET transaction isolation level

5. View lock Information
1. Execute exec sp_lock to report lock information.
2. Press Ctrl + 2 in the query analyzer to view the lock information.

6. Strange SQL statements
Java code
Begin tran
Update titles set title_idid = title_id where 1 = 2
If (selectavg (price) fromtitles)> $15
Begin
Update titles set price = price * 1.10
Where price <(select AVG (price) from titles)
End
Commit tran

Begin tran
Update titles set title_idid = title_id where 1 = 2
If (selectavg (price) fromtitles)> $15
Begin
Update titles set price = price * 1.10
Where price <(select AVG (price) from titles)
End
Commit tran

Update titles set title_idid = title_id where 1 = 2. This condition will never be true. What is the meaning of this write?
The where clause here looks strange, although the calculated result is always false. When the optimizer processes this query, because it cannot find any valid Sarg, its query plan will force an exclusive lock to scan the table. When the transaction is executed, the where clause immediately obtains a false value, so it does not perform an actual scan, but the process still gets an exclusive table lock.
Because this process now has an exclusive table lock, it can ensure that no other transaction will modify any data rows and can perform repeated read, and avoid potential deadlocks caused by holdlock.
However, when table locking is used to minimize deadlocks, contention for table locking is also increased. Therefore, before implementing this method, you need to consider whether to avoid deadlocks is more important than allowing concurrent access to tables.
Therefore, in this transaction, no other process modifies the price of any row in the table.

7. How to avoid deadlocks
1. When using a transaction, try to shorten the logical processing process of the transaction and commit or roll back the transaction as soon as possible;
2. Set the deadlock timeout parameter to a reasonable range, for example, 3 minutes to 10 minutes. If the timeout period is exceeded, the operation is automatically abandoned to avoid process suspension;
3. All SP servers must handle errors (via @ error)
4. Do not modify the default transaction level of SQL Server. Force lock not recommended
5. Optimize the program and check and avoid deadlock;
1) Reasonably arrange the table access sequence
2) Avoid user intervention in transactions as much as possible, and minimize the number of tasks processed by a transaction.
3) use dirty read technology. Dirty reads avoid lock conflicts because they do not lock the accessed table. In the Client/Server application environment, some transactions are often not allowed to read dirty data, but under specific conditions, we can use dirty read.
4) Time-Domain discretization for data access. The time-domain Discretization Method for Data Access refers to various control measures used in the Client/Server structure to control the access time periods for objects in the database or database. The following methods are used to implement the implementation: Reasonably arrange the execution time of background transactions, and uniformly manage background transactions using workflows. When a workflow manages a task, it restricts the number of threads of the same type of task (usually one) to prevent excessive resource occupation. On the other hand, it rationally schedules the time series and times of running different tasks, avoid executing multiple background tasks at the same time, and avoid running background tasks at the front-end transaction peak hours.
5) data storage space discretization method. The data storage space discretization method is used to distribute data in a logical table to several discrete spaces, so as to improve the table access performance. The following methods are used: first, a large table is divided into several small tables by row or column. Second, it is divided by different user groups.
6) Use a minimum isolation level. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, committed read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocks due to conflicts between transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

8. How to lock rows, tables, and databases
1. How to lock a row in a table
Java code
SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'

SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'
2. Lock a table in the database
Select col1 from table (tablockx) where 1 = 1;
No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback
3. Instance
Create a table
Java code
Create Table Table1 (A varchar (50) not null, B varchar (50), c varchar (50 ));
Create Table Table2 (d varchar (50), E varchar (50 ))
Insert Table1 (a, B, c) values ('a1', 'b1', 'c1 ');
Insert Table1 (a, B, c) values ('a2 ', 'b2', 'c2 ');
Insert Table1 (a, B, c) values ('a3 ', 'b3', 'c3 ');
Insert Table2 (d, e) values ('d1 ', 'e1 ');
Insert Table2 (d, e) values ('d2 ', 'E2 ');

Create Table Table1 (A varchar (50) not null, B varchar (50), c varchar (50 ));
Create Table Table2 (d varchar (50), E varchar (50 ))
Insert Table1 (a, B, c) values ('a1', 'b1', 'c1 ');
Insert Table1 (a, B, c) values ('a2 ', 'b2', 'c2 ');
Insert Table1 (a, B, c) values ('a3 ', 'b3', 'c3 ');
Insert Table2 (d, e) values ('d1 ', 'e1 ');
Insert Table2 (d, e) values ('d2 ', 'E2 ');
1) exclusive lock
Java code
-- Transaction a first updates table 1 and excludes other transactions during the update.
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first updates table 2
Begin tran
Select * From Table1 where B = 'b2 ';
Commit tran

-- Transaction a first updates table 1 and excludes other transactions during the update.
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first updates table 2
Begin tran
Select * From Table1 where B = 'b2 ';
If commit Tran executes the preceding two transactions at the same time, the SELECT query must wait until the update operation is complete before execution, that is, 30 seconds.
2) shared lock
Java code
-- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from modifying the table.
Begin tran
Select * From Table1 holdlock where B = 'b2 ';
-Holdlock: artificially locked
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first queries table 1 and then changes table 1
Begin tran
Select a, c from Table1 where B = 'b2 ';
Update Table1 set a = 'A' where B = 'b2 ';
Commit tran

-- Transaction a first queries the table 1 Table and adds a shared lock during the query to prevent other transactions from modifying the table.
Begin tran
Select * From Table1 holdlock where B = 'b2 ';
-Holdlock: artificially locked
Waitfor delay '00: 00: 30'; -- wait 30 seconds
Commit tran
-- Transaction a first queries table 1 and then changes table 1
Begin tran
Select a, c from Table1 where B = 'b2 ';
Update Table1 set a = 'A' where B = 'b2 ';
If commit Tran executes the preceding two transactions concurrently, the SELECT query in transaction B can be executed, and update can only be executed after the first transaction releases the shared lock and converts it to the exclusive lock.
3) deadlock
Java code
-- Transaction a updates table 1 with a delay of 30 seconds, and then updates table 2;
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
-- This will generate an exclusive row lock in Table1 until the transaction is completed.
Waitfor delay '00: 00: 30 ';
-- Entry latency
Update Table2 set D = 'd5 'where E = 'e1 ';
Commit tran
-- Transaction B First updates table 2, then latencies of 10 seconds, and then updates table 1;
Begin tran
Update Table2 set D = 'd5 'where E = 'e1 ';
-- This will generate an exclusive row lock in Table2 until the transaction is completed.
Waitfor delay '00: 00: 10'
-- Entry latency
Update Table1 set a = 'A' where B = 'b2 ';
Commit tran

-- Transaction a updates table 1 with a delay of 30 seconds, and then updates table 2;
Begin tran
Update Table1 set a = 'A' where B = 'b2 ';
-- This will generate an exclusive row lock in Table1 until the transaction is completed.
Waitfor delay '00: 00: 30 ';
-- Entry latency
Update Table2 set D = 'd5 'where E = 'e1 ';
Commit tran
-- Transaction B First updates table 2, then latencies of 10 seconds, and then updates table 1;
Begin tran
Update Table2 set D = 'd5 'where E = 'e1 ';
-- This will generate an exclusive row lock in Table2 until the transaction is completed.
Waitfor delay '00: 00: 10'
-- Entry latency
Update Table1 set a = 'A' where B = 'b2 ';
If commit Tran executes the preceding two transactions concurrently, both transactions A and B will wait for the other party to release the exclusive lock, resulting in a deadlock.

9. Table-level locks provided by sqlserver
The table-level locking tips specified by sqlserver are as follows:
1. holdlock: Keep the shared lock on the table until the entire transaction ends, instead of releasing the added lock immediately after the statement is executed.
2. nolock: no shared or exclusive locks are added. When this option takes effect, uncommitted data or "dirty data" may be read. This option is only applicable to select statements.
3. paglock: Specify to add a page lock (otherwise, a table lock may be added)
4. readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level.
5. readpast: Skip the data rows that have been locked. This option will allow the transaction to skip the data rows that have been locked by other transactions when reading data, rather than blocking until other transactions release the lock, readpast is only applicable to select statement operations in transaction operations at the Read committed isolation level.
6. readuncommitted: equivalent to nolock.
7. repeatableread: Set the transaction to a read-only isolation level.
8. rowlock: Use row-level locks instead of coarse-grained page-level locks and table-level locks.
9. serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.
10. tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both holdlock is specified, the lock remains until the transaction ends.
11. tablockx: Specifies the use of the exclusive lock on the table. This lock can prevent other transactions from reading or updating data in the table until the statement or the entire transaction ends.
12. updlock: Specify the update lock when reading data in the table, instead of the shared lock. The lock remains until this statement or the entire transaction ends, updlock is used to allow users to read data first (without blocking other users from reading data), and ensure that the data is not modified by other users during this period of time when the data is updated later.
Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.
Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

10. Application lock

Application locks are the locks generated by client code, rather than the two system stored procedures generated by SQL Server to process application locks.
Sp_getapplock: Lock application resources
Sp_releaseapplock: Unlock application resources
) Use the isolation level as low as possible. Isolation level refers to the degree of multi-user transaction isolation to ensure the integrity and consistency of database data. sql92 defines four isolation levels: uncommitted read, submit read, Repeatable read, and serializable. If a high isolation level is selected, such as serializability, although the system can guarantee data integrity and consistency to a greater extent for better isolation, however, the chance of deadlocked due to conflicting transactions is greatly increased, which greatly affects the system performance.
7) Use bound connections. Bound connections allows two or more transactions to connect to share transactions and locks, and any transaction connection must apply for locks as if another transaction wants to apply for locks, therefore, you can allow these transactions to share data without locking conflicts.
8) Consider using Optimistic Locking or giving the transaction an exclusive lock first.

8. How to lock rows, tables, and databases
1. How to lock a row in a table
Java code
SET transaction isolation level read uncommitted
Select * From Table1 rowlock where a = 'a1'

It would be better if there are more detailed descriptions of the lock corresponding to the isolation level.