DB concurrency control _notes

• 11.1 Concurrency control Overview 11.2 Blockade (locking) • 11.3 Live locks and deadlocks • 11.4 Serializable of concurrent scheduling • 11.5 Two-stage lock protocol • 11.6 The size of the blockade 11.1 Concurrency Control Overview 11.2 Blockade (locking) • Multi-transaction execution mode:· (1) Transaction serial execution · (2) Cross concurrent mode · (3) Concurrent mode • Data inconsistency resulting from concurrent operations: • Missing Modifications (lost update) • Non-repeatable reading (non-repeatable read) • Read "Dirty" data (dirty Read) • Key Technologies for concurrency control: • Blockade (locking)(Commercial DBMS generally use the blocking method) • Time stamp (Timestamp) • Optimistic control method • Basic blockade type:· Exclusive Lock (Exclusive lock, denoted for x lock) • Shared Lock (Share lock, denoted for S lock) • Blockade protocol (locking Protocol) • Commonly used blockade protocol: level three blockade protocol. • First-level blockade protocol• Transaction T must have an X lock on the data R before it is modified. It will not be released until the end of the transaction. • First-level blockade protocol prevents loss of modification • In a first-level blockade protocol, if it is read data, it does not need to be locked, so it cannot avoid reading and reading "dirty" data without duplication. • Level two blockade protocol• First-level blockade protocol + transaction T must add s lock before reading data R after reading, you can release the S lock without waiting for the end of the transaction. • The two-level blockade protocol prevents loss of modifications and reads of "dirty" data. • In the two-level blockade protocol, the S lock can be released after reading the data, so it cannot avoid being read again. • Level three blockade protocol• Three-level blockade protocol + transaction T before you read data R, you must add s lock to it until the end of the transaction. The level three blockade protocol avoids loss of modification, reads "Dirty" data, and cannot be read again. concurrency mechanism for SQL Server· Transaction Isolation• For Programming Staff, the lock is automatically managed by setting the isolation level of the transaction without having to manually set the control lock.

Isolation level	Dirty Read	Missing modification ( virtual read )	Non-repeatable reads	Phantom (two cases of non-repeatable reads)
Read not submitted	Can't avoid	Can't avoid	Can't avoid	Can't avoid
Submit Read	Can avoid	Not sure	Can't avoid	Can't avoid
REPEATABLE READ	Can avoid	Can avoid	Can avoid	Can't avoid
can be read serially	Can avoid	Can avoid	Can avoid	Can avoid

· SET TRANSACTION Isolation Level READ Uncommitted READ committed REPEATABLE Read SERIALIZABLE Live lock and deadlock Avoid live locksFirst-come first-served policy (queue) Resolve Deadlock·1. Take certain measures to prevent the occurrence of deadlock. 2. Allow deadlock to occur, take certain methods to diagnose deadlock, unlock deadlock. • Deadlock Prevention• A blocking law that requires each transaction to lock all data to be used at once, otherwise, you cannot proceed. • The problem of a blockade law: • Lock all data that will be used later, which is bound to widen the scope of the blockade, thus reducing the concurrency of the system. • Sequential Blockade method: · Set a blocking order for the data object beforehand, all matters are blocked in this order. • The problem of the sequential blockade method the data objects that can be blocked in the database system are extremely numerous and changing, so it is very difficult and costly to maintain the locking order of such a large and varied resource. A blocking request for a transaction can be determined dynamically as the transaction is executed, making it difficult to determine in advance which objects each transaction is to block, and therefore it is difficult to impose a blockade in the order in which it is ordered. • Deadlock Diagnostics• Timeout Method: • If a transaction waits longer than the specified time limit, I think there's a deadlock. Advantages: Implementation Simple • Disadvantage • It is possible to misjudge deadlocks. If the time limit is set too long, the deadlock can not be discovered in time. • Wait diagram: • Dynamically reflect the waiting situation of all transactions with transaction waiting graphs · A transaction wait graph is a g= (t,u) concurrency control subsystem that periodically (for example, every 1min) detects a transaction waiting graph, and if a loop is found in the diagram, a deadlock occurs in the system. • Removal of deadlocks• Select a transaction that handles the lowest cost of the deadlock, undo it, and release all locks held by this transaction so that other transactions can continue to run.