Introduction of database System--11. Concurrency mechanism

1. Background

Transactions require different resources during execution, sometimes requiring a CPU, sometimes I/O, and sometimes communication is required. If the transaction is executed serially, many system resources will be in an idle state.

In a single processor system, the parallel execution of the transaction is actually the parallel operation of these parallel transactions, which is called the cross-concurrency mode.

In multiprocessor system, each processor can run a transaction, and multiple processors can run multiple transactions simultaneously, which is called concurrent concurrency.

2. Concurrency type

Concurrent transactions can result in inconsistent data, mainly including loss of modifications, non-repeatable reads, and read "dirty" data

Concurrency control enables transaction isolation and consistency

2.1 Loss of modifications

T1,T2 changes to a transaction may be lost if the same data is read in the same way for the same modification

2.2 Non-REPEATABLE READ

T2 Read First, T1 Modify, T2 re-read, single record two times inconsistent data;

T2 Read First, T1 Delete, T2 re-read, record number decreases or does not exist;

T2 read First, T1 new, T2 re-read, the number of records increased;

2.3 Reading Dirty Data

T1 first Modify, T2 read, T1 for some reason is revoked, T2 read data for dirty data

3. Concurrent Processing

The main technologies are blocking, timestamp, optimistic control, and multi-version concurrency control.

3.1 Blockade

Principle

A transaction t makes a request to the system before the operation of a data object (such as a table, a record), locks it, and then the transaction T does not update the data object until the lock is released.

Block type

Exclusive lock (x Lock): Transaction T to data Object D plus x lock, other transaction cannot read and write the data object D

Shared lock (S lock): Transaction T to data Object D plus s lock, other transactions can read D, but not writable D

Blockade protocol

First-level blockade protocol

T add x Lock before modifying D, release after end

Can resolve lost modifications

Second-level blockade protocol

T add x Lock before modifying D, release after end; t locks the s before reading D, and reads out the release

Can resolve lost modifications + read "dirty" data

Level three lockdown protocol

T add x Lock before modifying D, release after end, T before reading D, add s lock, T end release

Can resolve lost modifications + read "dirty" Data + non-repeatable read

Live Lock and Deadlock

Live lock

Multiple transactions are queued for execution, and no priority policy causes transactions after the transaction T to execute before T, and T waits, called a live lock

Solution: Adopt "first come first service" strategy

Dead lock

T1 blocked the R1,t2 blockade R2,t1 and requested the blockade R2,t2 and requested the blockade R1, is in a deadlock state.

Workaround used by the operating system

A blockade act.

Requires that each transaction must be chained to all data to be used at once, otherwise it cannot continue.

Cons: Reduced DBS concurrency

Order Block Hair

Pre-set a blocking order on the data object, all transactions in this order to implement the blockade

Cons: Maintaining resource blocking order costs too high

Database-based solutions (diagnosing and unlocking deadlocks)

Timeout method

Sets the wait time for a transaction, which is considered dead lock

Cons: Bad waiting time setting

Wait Graph method

The concurrency control subsystem periodically generates a transaction wait graph and detects if a loop is present, indicating a deadlock

Serializable scheduling

Concept

The result of multiple transactions executing concurrently through a scheduling policy is the same as the result of executing those transactions serially in a sequential order. This scheduling strategy is called serializable scheduling.

Serializable is the criterion of concurrent transaction scheduling.

Conflict actions

Read and write operations on the same data by different transactions

Conflicting serializable scheduling

A scheduling SC, in order to ensure that the sequence of conflicting operations is not changed, by exchanging two transactions in the order of non-conflicting operations to get another dispatch SC ', if SC ' is serial, then called the scheduling SC for the conflict serializable scheduling.

SC1=R1 (a) W1 (a) R2 (a) W2 (a) R1 (b) W1 (b) R2 (b) W2 (b)

You can change the non-conflicting operation of two transactions R2 (a) W2 (a) R1 (b) W1 (b) to obtain the following serial dispatch

sc2= R1 (a) W1 (a) R1 (b) W1 (b) R2 (a) W2 (a) R2 (b) W2 (b)

The SC1 is the conflict serializable scheduling

The conflict serializable scheduling is a sufficient condition for serializable scheduling, not a necessary condition. As follows

L1=w1 (y) W1 (x) W2 (y) W2 (x) W3 (x) is a serial dispatch

l2= W1 (y) W2 (y) W1 (x) W2 (x) W3 (x) are conflicting serializable schedules and also serializable schedules

l3= W1 (y) W2 (y W2 (x)) W1 (x) W3 (x) is not a conflicting serializable schedule, is serializable

Two-stage lock protocol

Function: Serialization scheduling is realized

Principle: All transactions must lock and unlock data items in two stages.

The lock is obtained before any data is read or written;

After releasing a blockade, you can no longer apply for any form of lock.

similarities and differences with a blockade method:

The locking time points agreed on both ends may not be the same moment, and a blockade law must be at the same time;

It is still possible to have a deadlock by following both sides of the agreement.

Block granularity

Concept

The size of the blocked object is called the blocking granularity. such as attribute values, tuples, relationships, indexes and other logical units, or data pages, index pages, physical records, and other physical units.

the greater the granularity of the blockade, the smaller the concurrency, the smaller the system overhead

Multi-granularity blocking

Multi-granularity tree: The root node is the entire database, the leaf node is the smallest data granularity, note: Maximum minimum self control.

The multi-granularity blocking protocol allows each node in a multi-granularity tree to be locked independently, but a node lock means that all descendant nodes of the node are locked in the same way.

Explicit blocking: Locks that are added directly to the data object

Implicit blocking: The data object is locked because the parent node is locked

Intent lock

Background: Due to the low efficiency of checking for explicit blocking (explicitly checking all descendant nodes, implicitly checking all ancestor nodes), an intent lock is introduced.

Concept: When you lock any node, you must first add an intent lock to its upper node.

Type

Intent shared Lock (is lock)

For a node plus s lock, the upper node of it is locked

Intent exclusive Lock (IX lock)

An x lock is added to a node, and an IX lock is added to its upper node

Shared Intent exclusive Lock (six lock)

If you add six locks to a table, it means that the transaction needs to read the entire table (so the entire table S lock), and the partial tuple is updated (so you want to add IX locks)

Compatible matrices for intent locks

Introduction of database System--11. Concurrency mechanism