Database management (transactional, ACID, concurrency, blocking, serializable, isolated)

1. Database transactions

1.1 Database Transaction refers to a series of operations performed as a single logical unit of work.

4 features (ACID) for 1.2 transactions:
(1) atomicity (atomic) (atomicity) transactions must be atomic units of work, and for their data modifications, they are either all executed or not executed. Typically, the operations associated with a transaction have a common purpose and are interdependent. Atomicity eliminates the possibility of a system processing a subset of operations.
(2) The consistency (consistent) (consistency) transaction must be completed to keep all data in a consistent state. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct.
(3) Isolation (insulation) (isolation) changes made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called serializable because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution. The highest isolation level is obtained when the transaction is serializable. At this level, the results obtained from a set of transactions that can be executed in parallel are the same as those obtained by running each transaction continuously. Because high isolation limits the number of transactions that can be executed in parallel, some applications reduce the isolation level in exchange for greater throughput. Prevent data loss.
(4) After the Persistence (Duration) (durability) transaction is complete, its effect on the system is permanent. This modification will persist even if a fatal system failure occurs.

There are 3 models of 1.3 transactions:
(1) Implicit transactions mean that each data manipulation statement automatically becomes a transaction, with no explicit start and end tags for each transaction.
(2) An explicit transaction is a transaction that has an explicit start-end tag, or the beginning is implicit, and the end of the transaction is explicitly marked. (BEGIN TRANSACTION transaction starts--COMMIT transaction ends--rollback transaction error rollback)
(3) Automatic transactions are automatically default by the system, beginning and ending without marking.

2. Concurrency control

2.1 Common concurrency concurrency consistency issues include: Lost Modifications (lost update), read dirty data (dirty Read), non-repeatable read (unrepeatable read), Phantom reads (Phantom Read, also called Phantom reads, Phantom reads, non-conformance reads, Or ghost data, often with non-repeatable reads.

2.2 To address concurrency inconsistencies, the SQL standard defines 4 types of isolation levels, including specific rules that define which changes within and outside a transaction are visible and which are not. Low-level isolation levels generally support higher concurrency processing and have lower system overhead.
(1) readuncommitted (Read UNCOMMITTED content) at that isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in real-world applications because it has no better performance than other levels. Reading uncommitted data is also known as dirty reading.
(2) ReadCommitted (read commit content) This is the default isolation level for most database systems (such as sqlsever,oracle, but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.
(3) RepeatableRead (can be reread) this is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. In theory, however, this can lead to Phantom reading.
(4) Serializable (Serializable) This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to collide with each other. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.

	Dirty reading Dirty Read	Non-repeatable reading non-repeatable read	Error read/Phantom read/virtual read Phantom read
Read non-submitted READ uncommitted	√	√	√
Read submitted by the Read Committed	X	√	√
REPEATABLE READ REPEATABLE READ	X	X	√
Serializable Serializable	X	X	X

2.3 To reflect the isolation level, the database uses blocking technology (locking)

(1) s lock, Share Locks, shared lock , read lock, Locked object can be read by lock transaction, but cannot be modified, other transaction can also add S lock on top.
(2) X locks, Exclusive Locks, exclusive locks , write locks, locked objects can only be read and modified by the transaction holding locks, other transactions cannot add other locks on the object, and cannot read and modify the object.

2.4 The introduction of blocking technology brings a "deadlock" problem

Two types of methods to resolve deadlocks:
(1) Prevention Law: A Blockade law (each transaction must lock the data used, otherwise it cannot be executed) and the sequential blocking method (locking the data used in the pre-set order).
(2) Diagnostic Cancellation Method: Timeout Method (a transaction exceeds the specified time to determine the deadlock) and the Wait graph method (the transaction wait graph is a graph g= (t,u), T is a set of nodes, each node represents a running transaction; U is a set of edges. Each edge represents the case of a transaction wait. If the transaction T1 waits for a transaction T2, there is a directed edge between the T1,t2, which points to T2 from T1. If a loop is found in the diagram, a deadlock is present in the system.

2.5 Blockade Protocol (Locking Protocol)

2.5.1 three-level blocking protocol for blocking protocols that guarantee data consistency

Incorrect scheduling of concurrent operations can result in three data inconsistencies: loss of modification, non-repeatable read and read of "dirty" data. The three-level blockade protocol solves this problem in varying degrees, respectively.
(1) Level 1 Blocking Protocol : Transaction T modifies data R before it is released until the end of the transaction. The end of a transaction consists of a normal end and an abnormal end. A Level 1 blocking protocol prevents loss of modification and guarantees that transaction T is recoverable. In a Level 1 blocking protocol, if only the read data does not modify it, it does not need to be locked, so it does not guarantee repeatable read and not read "dirty" data.
(2) Level 2 Blocking Protocol : Level 1 blocking protocol plus transaction T before reading the data r must be a lock on it, after reading can release S lock. Level 2 Blocking protocol further prevents the reading of "dirty" data , in addition to preventing loss of modification . In the Level 2 blocking protocol, the S lock can be released after reading the data, so it is not guaranteed to take care of repetition.
(3) Level 3 Blocking Protocol : Level 1 Blocking protocol plus the transaction T must have a s lock on the data R before it is read, until the result of the transaction is released. The Level 3 Lockdown protocol prevents non-repeatable reads In addition to preventing loss of modification and non-reading of "dirty" data .

2.5.2 Two-segment lock protocol for blocking protocols that guarantee parallel scheduling of serializable

The serializable is the only criterion for the correctness of parallel dispatching, and the two-segment lock (2PL) protocol is a blocking protocol to guarantee the serializable of parallel dispatching.
The two-stage locking protocol stipulates that:
Before any data is read or written, the transaction channel first obtains a blockade of that data, and in releasing a blockade, the transaction no longer obtains any other blockade.
The meaning of the so-called "two-paragraph" lock is that the transaction is divided into two phases, the first stage being the blockade, also known as the expansion phase, and the second phase releasing the blockade, also known as the contraction phase.

2.6 Serializable

Scheduling is a sequence in which important operations of one or more transactions are sorted by time.
If a scheduled action is the first of all actions of a transaction, then all the actions of another transaction, and so on, without the mixing of the actions, then we say that the dispatch is serial.
The correctness of the transaction principle tells us that each serial dispatch will maintain the consistency of the database state. In general, regardless of the initial state of the database, the effect of a dispatch on the state of the database is the same as a serial schedule, we say that the dispatch is serializable. [If the result of a parallel dispatch is equivalent to the result of a serial dispatch, then this parallel scheduling becomes serializable]

Database management (transactional, ACID, concurrency, blocking, serializable, isolated) (RPM)