Database-concurrency control

Tasks of the concurrency control mechanism

Correct scheduling of concurrent operations
Ensuring the isolation of transactions
Ensure consistency of the database

The existence of multi-user database system
A database system that allows multiple users to use concurrently
Aircraft Booking Database System
Bank database System
Features: Up to hundreds of transactions concurrently running at the same time
Different multi-transaction execution modes
(1) Transaction serial execution
Only one transaction runs at a time, and other transactions must wait until the end of the transaction before they can run
Can not make full use of system resources, and play the characteristics of database shared resources

(2) crossover concurrency mode (interleaved Concurrency)
In a single processor system, the parallel execution of the transaction is the parallel operation of these parallel transactions in turn cross-run
Parallel transactions in a single processor system do not actually run in parallel, but can reduce the idle time of the processing machine and improve the efficiency of the system.

(3) Simultaneous concurrent mode (simultaneous concurrency)
Multiprocessor system, each processor can run a transaction, multiple processors can run multiple transactions at the same time, enabling multiple transactions to actually run concurrently

Issues associated with transaction concurrency execution
Causes multiple transactions to access the same data at the same time
may access incorrect data, compromise transactional consistency and database consistency

Concurrency operations result in instances of inconsistencies in data [example1] An activity sequence in an aircraft booking system ① a ticketing point (a) read out the balance of a flight ticketA, setA= -② b Ticketing Point (b business) read out the balance of the ticket on the same flightA, but also for -③ a ticket to sell, revise the balanceA←A-1SoAFor the, putAWrite back to the database; ④ B ticket sales also sell a ticket, modify the balanceA←A-1SoAFor the, putAWrite back Database Results Two tickets sold, the balance of the ticket in the database only reduced1This is known as the inconsistency of the database and is caused by concurrent operations. In the case of concurrent operation, the operation sequence of two transactions of A and B is randomly dispatched. The modification of a transaction is lost if it is executed according to the schedule sequence above. Reason: section4Step b Transaction ModificationAAnd writes back after overwriting a transaction with the modified concurrency operation resulting in data inconsistency (incorrect) lost modification (Lost Update) non-repeatable read (non-repeatable read) read "dirty" data (Dirty Read) notationR (x):Read Data xW (x):Write data x Two transactions T1 and T2 read into the same data and modify, T2 submission results destroy the results of T1 commits, resulting in T1 modification is lost. The above examples of aircraft bookings are such

不可重复读是指事务T1读取数据后，事务T2    

Non-REPEATABLE READ

(2) Transaction T1 After some data records have been read from the database according to certain conditions, the transaction T2 deleted some of the records, and when T1 reads the data again by the same conditions, some records are found to disappear.
(3) Transaction T1 After some data records are read from the database on a certain condition, the transaction T2 inserts some records, and when T1 reads the data again in the same condition, it finds some more records.
The latter two types of non-repeatable reads are sometimes also called Phantom Phenomena (Phantom Row)

Read "Dirty" data

Reading "Dirty" data means:
Transaction T1 modifies a data and writes it back to disk
After the transaction T2 reads the same data, T1 is revoked for some reason
At this point T1 has modified the data recovery original value, T2 read the data is inconsistent with the data in the database
T2 read data is "dirty" data, that is, incorrect data
Data inconsistency: Due to concurrent operations that compromise the isolation of transactions
Concurrency control is the correct way to dispatch concurrent operations, so that the execution of a user's transaction is not affected by other transactions, so as to avoid the inconsistency of data

The main techniques of concurrency control
Blockade (Locking)
Timestamp (Timestamp)
Optimistic control method
Commercial DBMS typically uses a blocking approach

Blockade (Locking)

The main techniques of concurrency control
Blockade (Locking)
Timestamp (Timestamp)
Optimistic control method
Commercial DBMS typically uses a blocking approach

What kind of control a transaction has after locking a data object is determined by the type of blocking.
Basic block type
Exclusive Lock (EXclusive Locks, précis-writers x Lock)
Shared Lock (Share Locks, précis-writers s lock)

The lock is also called a write lock.
If the transaction t has an X lock on the data object A, then only t can read and modify a, and no other transaction will be able to add any type of lock to a, until T releases the lock on a
Ensure that other transactions cannot read and modify a before the lock on T releases a

Shared locks are also called read locks
If the transaction t adds the S lock to the data object A, the other transaction can only be locked to a plus s, not the x lock, until T releases the S lock on a
Ensure that other transactions can read a, but cannot make any modifications to a before T releases the S lock on a

In the compatibility matrix of the Lock:
The leftmost column represents the type of lock on the data object that the transaction T1 has obtained, where the horizontal line indicates that no lock is added.
The top row indicates that another transaction T2 a blocking request to the same data object.
Can the T2 's blockade request be satisfied with the Y and N representations in the matrix
Y indicates that the closure requirements of the transaction T2 are compatible with the locks held by the T1, and the blocking request satisfies
n indicates that T2 's blocking request is in conflict with T1 's held lock, T2 's request was denied

Live Locks and deadlocks

Blocking technology can effectively solve the consistency problem of parallel operation, but also bring some new problems
Dead lock

事务T1封锁了数据R1T2封锁了数据R2T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1

Two types of methods
1. Preventing deadlocks
2. Diagnosis and release of deadlock

Live lock

Avoid live Lock: Adopt first-come-first-serve strategy
When multiple transaction requests block the same data object
Queue these transactions in the order in which they are requested to be blocked
Once the lock on the data object is released, the first transaction in the request queue is approved for a lock

Prevention of deadlocks

A deadlock occurs because two or more transactions have blocked some data objects, and then both request a lock on a data object that has been blocked for another transaction, resulting in a dead wait.
Preventing deadlocks is about destroying conditions that create deadlocks.
Ways to prevent deadlocks
A blockade act.
Sequential Blockade method

Requires that every transaction must be locked at once for all data to be used, otherwise it cannot continue execution
Problems that exist
Reduce system concurrency
Difficult to pinpoint the blocked object in advance

Sequential blocking is a pre-set order of blocking for data objects, numbered sequentially, and all transactions are filed in this number sequence for blocking.
Problems existing in sequential blockade law
Maintenance costs
The blocked data objects in the database system are very numerous and constantly changing.
Difficult to achieve: it is difficult to determine in advance which objects each transaction will block
Conclusion
The strategy of preventing deadlocks, which is widely adopted in the operating system, is not very suitable for database characteristics
The DBMS is more commonly used to diagnose and unlock deadlocks in the problem of deadlock resolution.

Diagnosis of Deadlocks
Timeout method

如果一个事务的等待时间超过了规定的时限，就认为发生了死锁优点：实现简单缺点有可能误判死锁时限若设置得太长，死锁发生后不能及时发现

Transaction Wait Graph method

用事务等待图动态反映所有事务的等待情况事务等待图是一个有向图G=(T，U)T为结点的集合，每个结点表示正运行的事务U为边的集合，每条边表示事务等待的情况若T1等待T2，则T1，T2之间划一条有向边，从T1指向T2

The concurrency control subsystem generates a transaction wait graph periodically (for example, every few seconds) to detect transactions. If a loop is found in the diagram, a deadlock is present in the system.

Database-concurrency control