PKU SQL Database Video Course notes

Jim gray-transaction processing:concepts and techniques
http://research.microsoft.com/~gray/

Transaction Concepts

Transaction definition
-A transaction is a program execution unit consisting of a sequence of sequences of operations that are either done or not, and are an inseparable unit of work.

Transaction characteristics (ACID)
* Atomicity (atomicity)
All of the operations contained in the transaction are either done entirely or not,
Atomicity is implemented by the recovery mechanism.

* Consistency (consistency)
The separation of transactions must ensure database consistency
The database is in a consistent state before the transaction begins; The database must remain in a consistent state after the transaction ends
The consistency state of the database is the responsibility of the user
If bank transfer, the sum of the amount of the account before and after the transfer should remain unchanged (salami)

* Isolation (Isolation)
The system must ensure that transactions are not affected by other concurrent execution transactions
For any pair of transactions T1, T2, in T1 's view, T2 either ended before T1 started, or started after T1 was completed
Isolation is achieved through concurrency control mechanisms

* Persistence (Durability)
Once a transaction is committed, its impact on the database must be permanent
System failure cannot change the persistence of a transaction
Persistence is achieved through a recovery mechanism

Show transactions-starting with BEGIN transaction, ending with commit or rollback
Implied transaction-transaction starts automatically until a commit or rollback is encountered
Automatic transactions-Each data manipulation statement acts as a transaction

Planar transaction-one layer structure begin tran.....commit
Disadvantages of planar transactions: cannot be partially rolled back

Nested transactions-transactions are included in the transaction

Distributed transactions
N a t,n DBMS

Transaction scheduling
The order of execution of a transaction is called a dispatch, which indicates the order in which the order of things is executed in the system
Scheduling of a set of transactions must ensure that
Contains the operation instructions for all transactions;
The order of instructions in a transaction must remain unchanged.

Serial Scheduling
Parallel scheduling

Recoverable schedule

Recovery of transactions:
A transaction has failed and should be able to undo the impact of the transaction on the database. If there are other transactions that read the data written by the failed transaction, the transaction should also be revoked

Level of transaction isolation:
Read dirty data
Cannot read repeatedly
Illusion occurred

Definition of isolation level in SQL
Serializable
REPEATABLE READ
Read Committed
Read uncomitted

The criteria for the correctness of a transaction's dispatch are:
A concurrent execution schedule should be equivalent to a serial dispatch of this set of transactions

Conflict serializable

* Conflict directives
When two instructions are operations on the same data item for different transactions, and at least one of them is a write instruction,
That the two directives are conflicting.

Non-conflicting instruction Exchange order does not affect the final result of the dispatch

* Conflict equivalence
If the dispatch s can be converted to dispatch s ' by a series of non-conflicting instruction exchanges, then the dispatch s and S ' are conflicting equivalents.

Conflict serializable
When a dispatch s is equivalent to a serial scheduling conflict, it is said that the scheduling is a conflict serializable

The decision of conflict serializable
Priority map (precedence Gragh)

* * Views can be serialized * *

* View equivalence
Consider the two schedules for a transaction set, S ', if dispatch s,s ' meet the following conditions, they are called view equivalents:
1. For each data item q, if the transaction Ti reads the initial value of Q in dispatch S,
Then TI must also read the initial value of Q in dispatch s '.
2. For each data item Q, if the transaction TI executes read (Q) in dispatch s, and the value read is generated by TJ,
Then the Q value that Ti reads in dispatch s ' must also be generated by TJ.
3. For each data item Q, if there is a transaction in dispatch s that executes the last write (q),
The transaction must also perform the last write (Q) in dispatch s '.

* Views can be serialized
If a scheduling view is equivalent to a serial schedule, the schedule is said to be serializable for the view
Conflicting serializable scheduling must be the view serializable
There is a serializable, but non-conflicting, serialization scheduling of views

* Construction of tagged priority graphs
Set Schedule s contains transaction {T1, T2, ..., Tn}, set TB, TF is two virtual transactions, where TB is all write (Q) operations in S,
TF for all read (Q) operations in S. Insert TB at the beginning of dispatch s, insert TF at the end of dispatch S,
Get a new dispatch s '.
1. If Tj reads the value of the data item that TI writes, it joins the Edge TI--0->TJ
2. Delete all edges of the associated useless transaction. If the path from TI to TF does not exist in the priority graph, then TI is a useless transaction.
3. For each data item Q, if TJ reads the Q value of Ti writes, TK performs a write (Q) operation and TK!=TB:
1) If Ti = TB and TJ! = Tf, insert the edge in the tagged priority graph tj-0-> Tk
<TK, Tb, tj> (x not allowed) <TB, Tk, tj> (x not allowed) <TB, Tj, Tk > (Allowed)
2) If ti! = Tb and TJ = Tf, insert edge in tagged priority tk-0-> Ti
<ti, TF, TK > (not allowed), <ti, TK, Tf > (not allowed), <TK, Ti, TF > (allowed),
3) If ti! = Tb and Tj! = Tf, insert the Edge tk-p-> Ti and tj-p-> Tk in the tagged precedence graph.
where P is a unique integer greater than 0 that has not been used in the tag of the front edge.
<ti, TK, Tj > (not allowed), <TK, Ti, Tj > (Allow), <ti, Tk, Tf > (Allowed)

Q: What does this virtual transaction do?

Figure: A closed loop, not a view can be serialized.

Figure: The loop is not present, the view is serializable.??

Definition of the blockade
A blockade is a transaction that locks a data object, obtains a certain amount of control over it,
Restricts the use of other transactions to the data object.

To access a data item R, the transaction TI must first request a blockade of R, if R has been added by the transaction TJ incompatible locks,
Then ti needs to wait until TJ releases its blockade.

Types of closures
Exclusive lock (x lock, EXclusive Lock)
-Write lock
Request an exclusive lock on R: Lock-x (R)

Shared lock (S lock, Share Lock)
-Read lock
Request a shared lock on R: Lock-s (R)

* * Two-phase blockade protocol * *
Two-phase Locking Protocol
* Two-phase blockade protocol content
Growth phase (growing Phase)-transactions can acquire locks, but cannot release locks
Reduction phase (Shrinking Phase)-The transaction can release the lock, but cannot get the lock

Example:
Lock-s (A) ... lock-s (B) ... lock-x (C) ...
Unlock (A) ... unlock (C) ... unlock (B) ...
Compliance with two-phase lock protocol

Lock-s (a) ... unlock (a) ... lock-s (B) ...
Lock-x (c) ... unlock (c) ... unlock (B) ...
Non-compliance with two-phase lock protocol

* Two-phase blockade protocol
* Blockade point: The time when the transaction gets its last blockade
* Transaction scheduling is equivalent to a serial schedule consistent with their blocking point sequence
* Make {T0, T1, ..., Tn} is a transaction set that participates in scheduling s, if TI has a type a lock on the data item R Plus
TJ to the data item R plus type B lock, and comp (A, A, a) =false, then said Ti before Tj, remember to do TI->TJ,
Get a priority map
* Ti is Ti is the blocking point, if ti-, TJ, ti < TJ
* If {T0, T1, ..., Tn} is not serializable then there is a ring in the priority graph, it may be set to T0->t1->...->tn->t0,
Then t0<t1<...<tn<t0, contradiction

* Locks held until the end of a transaction are called long locks
* Locks that can be released in the middle of a transaction are called short locks
Two-stage blockade + short X Lock + short S lock

(Definition of isolation level in SQL
Serializable
REPEATABLE READ
Read Committed
Read uncomitted)

Repeatable READ-The "non-repeatable read" condition is guaranteed only when the X and S locks are long locks

* Lock Conversion
* Two-segment lock protocol with lock conversion
* Growth phase
Available for Lock-s
Available for Lock-x
Lock-s can be upgraded to Lock-x (upgrade)
* Reduction phase
Can release lock-s
Can release lock-x
Lock-x can be upgraded to Lock-s (downgrade)

Block granularity
* Blocked objects
Attribute values, collection of attribute values, tuples, relationships, an index entry, entire index, entire data set, physical page, block
* Large block size, low concurrency, simple blocking mechanism, small overhead
* The size of the blockade is small, the concurrency is high, the blockade mechanism is complex and expensive.
* Ideally, only data objects that are related to the specified operation are blocked, and these data objects are called transactional integrity-related domains.

* Data objects have a hierarchical relationship from large to small, and when the outer data object is blocked, it also means that it is blocked at the same time.
All the inner-layer data objects.
Database--section--a tuple

* Intention (reservation) granularity
* In a layered block, blocking the upper node means blocking all the inner nodes. If there is a transaction T1 adds a tuple
s lock, and the transaction T2 an X lock on the relationship where the tuple is located, so that the implied x blocks the tuple, which creates a contradiction.
* Introduction of Intent Lock I (intend): When a node is added with an I lock, indicating that some of its inner nodes have a de facto blockade,
Prevent other transactions from explicitly blocking the node.
* The implementation of the I lock is from the root of the blocking hierarchy, which occupies all the nodes on the path sequentially,
Until the parent node of the node to be really explicitly blocked.

* Compatibility matrix
I compatible with I
s compatible with S
Other incompatible

is lock
If you add an IS lock to a data object, it indicates its descendant node quasi (intent) plus s lock
For example, to add an S lock to a tuple, you first add the is lock to the relationship and database

IX Lock
If an IX lock is added to a data object, its descendant nodes are intended (intent) plus x lock
For example, to add an S lock to a tuple, you first add an IX lock to the relationship and the database

More granular compatibility matrices
is IX S X

In the actual database, is lock does not exist??

Six locks
If a data object is added with a six lock, it is added to the S lock, plus the IX lock
* For example, by adding six locks to a table, the transaction reads the entire table (plus S lock on the table).
Individual tuples are updated (plus IX locks on the table)

SET TRANSACTION Isolation Level repeatable READ
BEGIN TRAN
SELECT * FROM BigTable
UPDATE bigtable
SET col = 0
WHERE KeyColumn = 100

Code Range Lock
* Code Range Locking principle
* Code range Locking principle solves the problem of phantom reading concurrency
* Code range locks cover a single record and the range between records,
You can prevent phantom insertions or deletions from a recordset that is accessed by a transaction.
* Code range locks work by overwriting the range between index rows and index rows (rather than locking the entire underlying table's rows).
Because the second transaction makes any row insertions within that range, the update or delete operation requires that the index be modified.
The code range lock overrides the index entry, so the second transaction is blocked before the first transaction completes.

* * LOCK Implementation * *
* Lock Manager
* Transaction sends a blocked request and release request to the lock manager
* Lock manager maintains a lock table records the lock's grant status and the blocking request in the waiting state
* Lock Table
* The lock table is generally used as a hash table in memory, indexed by the name of the blocked object

* Black Rectangle represents the lock that has been granted, white indicates a waiting blocking request
* Lock table record the type of lock at the same time
* New blocking requests are added to the end of the corresponding request queue and approved when the blocking request is compatible with the previous lock
* The request is removed from the queue when the blockade is released and check that subsequent requests are satisfied
* If the transaction is abandoned, all granted and pending lock requests are deleted
* For increased efficiency, the lock manager logs each transaction holding a lock

* If you look at locks
* Blocking Resources
* Lock manager knows nothing about resources, it's just "memcmp ()"
* Lock Resource format
Resource Type Database ID resource detail data
Object ID-Data Object
file#: page#-Block a page
file#: page#: Slot on Page-Block a tuple

* Rid:8 bytes (file#, page#, slot#)
The RID remains the same unless it is removed or moved elsewhere, and the RID can be reused if tuples are removed
RIDs can be used as a blocking resource

* Clustered Index
Rows can be identified by a unique aggregation code
The aggregation code can be used as a blocking resource

* Level Two Index
Code and location can be used as a blocking resource

* Lock Upgrade

* Deadlock
* Conditions for deadlocks to occur

* Methods to resolve deadlocks
* Prevent deadlocks
* Pre-Occupy all the resources needed, either all at once or all blocked
Cons: Difficult to predict which data to block and low data usage
* All resources are pre-ordered and transactions are blocked in the specified order
* Use preemption and transaction rollback to assign a timestamp to each transaction, if the lock requested by the transaction T2 has been T1 held,
You can compare the timestamp of the T1 with T2 to determine whether the T1 is rolled up and grant T2 the lock that is T1 released.

Q: Is the time stamp late for rollback?
If the lock requested by the transaction T2 has been held by T1, if the T2 timestamp is earlier than T1, the T1 is rolled back,
Let T2 execute, then execute T1
-that is, the time is early to execute first. The late rollback of the time-stamp yielded early.

(You can write a transaction by avoiding the method of writing, such as 2 transactions at the same time update a table, and then update the B table,
This only happens when the wait is not deadlocked. ）

* Deadlock Detection and recovery
* Timeout method
If the time to wait for the blockade exceeds the limit, the transaction is revoked
* Wait Graph method

* * Live Locks (live Lock) * *
* There may be a transaction that is always waiting, not executed, called a live lock (starved to death)
* T2 holds the S lock on R, T1 applies x lock on R, then T1 must wait for T2 release S lock;
T3 applies the S lock on R, it can get authorization blockade, so T1 must wait for T2,T3 release S lock
* The strategy of avoiding live lock is to follow the principle of "first come first service" and queue each transaction according to the order of blocking.
When the transaction TI pairs the data item R plus the M-type lock, the conditions for obtaining the blockade are:
1. There is no other transaction holding the lock with the M-lock conflict on R-(type conflict)
2. There is no transaction waiting for lock on R and request lock before TI-(time)

Note:fifs-first in First Service

* Blocking
Example: An x lock is added to a row, causing the second connection to be blocked with an IX lock request. Adding an index will not block it.
CREATE INDEX idx1 on test

* Database Failure
* Transaction Failure
* means that the operation of a transaction is terminated without reaching the desired end point
* Unexpected failure
1. Cannot be handled by a transactional program
Such as: An operation overflow, a deadlock occurs and is selected to revoke the transaction
* Predictable failure
1. The application can discover the transaction failure, and the application can let the transaction roll back
such as: When the transfer of money found insufficient

* System failure
* Soft fault (soft crash): Under the influence of hardware failure, software error, although the memory information is lost,
But did not destroy the data in external memory
Such as: CPU failure, sudden power outage, dbms,os, application and other abnormal termination
* Media failure
* Hard crash: Also known as disk failure, destroy the database on external memory,
and affects all transactions that are accessing this part of the data
such as: Disk head collision, instantaneous strong magnetic field interference

* Definition of recovery
* Recovery is the ability to restore a database from an error state to a correct state to ensure database consistency
* The basic principle of recovery is redundancy, that is, any part of the data in the database can be rebuilt based on redundant data stored elsewhere in the system

* Dump
* The process of copying a database to a tape or another disk to save it. These alternate data are called backup (backup) copies
* Static Dump
No access to the database is allowed during the dump, modify the activity
* Dynamic Dump
Allow any access or modification to the database during a dump
* Mass Dump
Dump all databases at a time
* Incremental Dump
Only data that has been updated since the last dump is dumped at a time

* Log
* Log files are files that are used as transaction units to record each update activity of the database and are automatically recorded by the system
* Log contents include: Record name, old record value, new record value, transaction identifier, operation identifier, etc.
* Transaction Ti Start, write log: <ti start>
* Before transaction Ti executes write (x), write log: <ti, X, V1, V2>,V1 is the value before x update, V2 is the value after x update
* After transaction Ti finishes, write log: <ti commit>

Example: see.

* Transaction Classification
* Successful Business
The commit identity of the transaction is recorded in the log file
* Premature Business
Only transaction BEGIN TRANSACTION ID in log file, no commit

* Basic Recovery operations
* Modifications performed by the successful firm should perform redo operations,
That is, the modified object is assigned a new record value.
Redo = Redo two Times Square

* Modifications made to the aborted office should perform undo operations,
That is, undo the operation and modify the object to be given the old record value

* The principle of writing logs first (WAL)
* For transactions that have not yet been committed, before the DB buffers are written to external memory,
You must first write the log buffer contents to external memory.
* Log records what changes will occur
* Write to DB indicates what changes actually occurred
* If the DB is written first, a system crash may occur midway through the write, causing the memory buffer contents to be lost.
While external memory db is in an inconsistent state, the DB cannot be recovered because the contents of the log buffer are corrupted.

* Transaction Failure recovery
* Undo the changes that the transaction has made to the database
* Measures
1. Reverse scan the log file to find the update operation for the transaction
2. Perform an inverse operation on the update of the transaction and write the old value before the transaction update to the database
3. Continue to reverse scan the log file, look for other update operations for the transaction, and do the same
4. This process continues until the start of the transaction is read, and the failback of the transaction is completed.

* System failure recovery
* Inconsistent status Reason
1. An update to the database that has not completed the transaction has been written to the database
2. Committed transaction updates to the database are not written to the database
* Measures
1. Scan the log file to find the perfect transaction and record it in the redo queue;
Find the aborted transaction, and write it down in the undo queue
2. Reverse scan log, undo operation for each log record of the transaction ti in the undo queue
3. Forward scan log file, perform redo operation on each log record of transaction ti in redo queue

Example: In the event of a system failure recovery, the database consistency can be maintained only after the undo is redo.

* Media failure recovery
* Data files and log files on disk are compromised
* Measures
1. Mount the latest backup copy of the database to restore the database to a consistent state at the time of the most recent dump.
2. Load the corresponding log file copy to redo the completed transaction

* Checkpoint (Checkpoint)
* When the system is restored, the entire log file needs to be searched to determine whether the transaction was aborted or completed.
1. The search process is too time consuming
2. Most transactions that need to be re-made have their updates written to the database. Although they do not have adverse consequences for their re-
But it will make the recovery process longer.
* Generate checkpoints
1. Output all log records currently in main memory to the stable
2. Output all modified buffer blocks to disk
3. Log <checkpoint> output to a stable

* Check Point function
* Avoid scanning the entire log file when recovering from failure
* Avoid redo two times
Ensure that the log is consistent with the database content before the checkpoint
* Do not need to perform a redo operation on a transaction committed before the checkpoint

* Check Point
* Minimum Recovery LSN (MinLSN), which is the smallest LSN in the following LSN:
LSN of checkpoint start point
LSN of the oldest active transaction starting point
* Checkpoint Generation
Checkpoints are automated by the system. The time interval for an automatic checkpoint is based on the number of records in the log, not the time

PKU SQL Database Video Course notes