Database-concurrency control

Database-concurrency control
Concurrent Control Task

Correctly schedule concurrent operations
Ensures transaction isolation
Ensure Database Consistency

Multi-User Database System
Database systems that can be used by multiple users at the same time
Aircraft ticketing Database System
Bank Database System
Features: hundreds of transactions can be concurrently run at the same time.
Different multi-transaction execution methods
(1) Serial transaction execution
Only one transaction runs at a time, and other transactions can run until the transaction ends.
Cannot make full use of system resources and give full play to the features of database shared resources

(2) Interleaved Concurrency)
In a single processor system, the parallel execution of transactions is the cross-running of parallel operations of these parallel transactions in turn.
Parallel transactions in a single processor system do not run in parallel, but they can reduce the idle time of the processor and improve the system efficiency.

(3) simultaneous concurrency (simultaneous concurrency)
In a multi-processor system, each processor can run one transaction. Multiple Processors can run multiple transactions at the same time to realize real parallel operation of multiple transactions.

Problems caused by concurrent transaction execution
Multiple transactions can access the same data at the same time.
It may access incorrect data and cause transaction consistency and database consistency to be damaged.

Example of data inconsistency caused by concurrent operations [Example 1] An activity sequence in the aircraft ticket booking system ① ticket A point (transaction A) reads the air ticket balance of A flight A and sets A to 16; ② B ticket site (B transaction) read the balance of the same flight ticket A, also is 16; ③ A ticket site to sell A ticket, modify the balance of A ← A-1, so A is 15, write A back to the database; 4 B ticket points also sell A ticket, modify the balance of A ← A-1, so A is 15, write A back to the database results clearly sell two tickets, the balance of air tickets in the database is reduced by 1. This is called database inconsistency, which is caused by concurrent operations. In the case of concurrent operations, the Operation Sequence of transactions A and B is scheduled randomly. If the preceding scheduling sequence is used, the modification of transaction a is lost. Cause: in step 1, transaction B modifies transaction A and writes it back, which overwrites the data inconsistency (inaccuracy) caused by concurrent modification operations of transaction A (Lost Update) non-repeatable Read: reads Dirty data (Dirty Read) Mark R (x): Read data xW (x ): write Data x two transactions T1 and T2 read the same data and modify it. The commit result of T2 destroys the submitted result of T1, resulting in the loss of modifications to T1. This is the case for plane ticket booking.

Non-repeated read means that after transaction T1 reads data, transaction T2 performs an update operation, so that T1 cannot reproduce the previous read results. There are three situations of non-repeated reads: (1) After transaction T1 reads a data, transaction T2 modifies it. When transaction T1 reads the data again, get a value different from the previous one.

Non-repeated read

(2) After transaction T1 reads some data records from the database based on certain conditions, transaction T2 deletes some of the records. When T1 reads data based on the same conditions again, some records have disappeared.
(3) After transaction T1 reads some data records from the database based on certain conditions, transaction T2 inserts some records. When T1 reads data based on the same conditions again, it finds that there are more records.
Phantom Row)

Read "dirty" Data

Reading "dirty" data refers:
Transaction T1 modifies a data and writes it back to the disk
After transaction T2 reads the same data, T1 is revoked for some reason.
At this time, T1 has modified the original data recovery value, and the data read by T2 is inconsistent with the data in the database.
The data read by T2 is "dirty", that is, incorrect data.
Data inconsistency: concurrent operations compromise the isolation of transactions.
Concurrency Control is to use the correct method to schedule concurrent operations so that the execution of a user's transaction is not disturbed by other transactions, so as to avoid data inconsistency.

Main Concurrency Control Technologies
Locking)
Timestamp)
Optimistic Control Method
Commercial DBMS generally adopts the blocking method.

Locking)

Main Concurrency Control Technologies
Locking)
Timestamp)
Optimistic Control Method
Commercial DBMS generally adopts the blocking method.

What kind of control a transaction has after locking a data object is determined by the blocking type.
Basic blocking type
EXclusive Locks (X lock)
Share Locks)

Exclusive locks are also called write locks.
If transaction T adds an X lock to the Data Object a, only T is allowed to read and modify A. No other transaction can add any type of lock to auntil T releases the lock on.
Ensure that other transactions cannot read or modify A before T releases the lock on.

Shared locks are also called read locks.
If transaction T adds the S lock to Data Object a, other transactions can only apply the S lock to a, but cannot apply the X lock until T releases the S lock on.
Ensure that other transactions can read A, but do not modify A before T releases the S lock on.

In the compatibility matrix of the lock:
The leftmost column indicates the type of the lock on the data object obtained by transaction T1. the horizontal line indicates no lock.
The top row indicates the blocking request sent by another transaction T2 to the same data object.
Whether the T2 blocking request can be expressed by Y and N in the Matrix
Y indicates that the locks of transaction T2 are compatible with the locks held by T1. Blocking requests can meet
N indicates that the T2 blocking request conflicts with the T1 lock, and the T2 request is rejected.

Active locks and deadlocks

Blocking technology can effectively solve the consistency problem of parallel operations, but it also brings some new problems.
Deadlock

Transaction T1 blocks data R1T2 blocks data R2T1 and requests to block R2, because T2 has blocked R2, so T1 waits for T2 to release the lock on R2 and T2 applies to block R1 again, because T1 has been blocked by R1, T2 can only wait for T1 to release the lock on R1. T1 is waiting for T2, while T2 is waiting for T1. Transactions T1 and T2 can never end, forming a deadlock.

Two methods
1. prevent deadlocks
2. deadlock diagnosis and Removal

Live lock

Transaction T1 blocks the data R transaction T2 and requests the R block, so T2 waits. T3 also requests to block R. When T1 releases the block on R, the system first approves the T3 request, and t2. T4 again requests to block R. After T3 releases the block on R, the system approves the T4 request ...... T2 may always wait. This is the case of a live lock.

Avoid live locks: Adopt the first-come-first-served policy
When multiple transaction requests block the same data object
Queue these transactions in order of request Blocking
Once the lock on the data object is released, first approve the application for the first transaction in the queue to obtain the lock.

Deadlock Prevention

The cause of the deadlock is that two or more transactions have blocked some data objects, and then all requests to lock the data objects that have been blocked by other transactions, resulting in a dead wait.
To prevent the occurrence of deadlocks, it is necessary to damage the conditions that cause deadlocks.
How to prevent deadlocks
Blocking Method
Sequential Blocking

Each transaction is required to lock all the data to be used at a time; otherwise, the execution cannot continue.
Problems
Reduce system concurrency
It is difficult to accurately determine the blocked object in advance

The sequential blocking method sets a blocking order for Data Objects in advance, which is numbered sequentially. All transactions apply for blocking in this order.
Problems with sequential Blocking
Maintenance Cost
There are a lot of blocked Data Objects in the database system, and they are constantly changing.
Hard to implement: it is difficult to determine the objects to block for each transaction in advance
Conclusion
The widely used Deadlock Prevention Strategy in the operating system is not very suitable for the characteristics of the database.
DBMS generally uses the method of diagnosing and removing deadlocks to solve deadlocks.

Deadlock Diagnosis
Timeout Method

If the wait time of a transaction exceeds the specified time limit, it is considered that the deadlock has the advantage: the implementation of simple disadvantages may be a false positive. If the deadlock time limit is set too long, it cannot be found in time after the deadlock occurs.

Transaction wait plot

The transaction wait diagram is used to dynamically reflect the waiting conditions of all transactions. The transaction wait diagram is a set of Directed Graph G = (T, U) T as nodes, each node indicates that the running transaction U is the set of edges, and each edge indicates the waiting state of the transaction. If T1 waits for T2, a directed edge is drawn between T1 and T2, point from T1 to T2

The concurrency control subsystem periodically (such as several seconds) generates a transaction wait diagram to detect transactions. If a loop exists in the figure, a deadlock occurs in the system.