Database principles-Basic concepts of transactions

2018-1-9 by Atlas

• Database access

Read (x): reads the data X from the disk's database into the memory buffer.
Write (x): Writes the data x, from the memory buffer to the disk's database.

• ACID Properties

1. atomicity (atomicity)
All operations of a transaction on data are an inseparable unit of work. These actions are either all executed or do nothing (as far as the effect of the DB is concerned).
The guarantee of atomicity is the responsibility of the database system itself, which is implemented by the DBMS's transaction management subsystem.
2. Consistency (consistency)
As a result of the independent execution of a transaction, the consistency of the database should be maintained, i.e. the data will not be compromised by the execution of the transaction.
Ensuring consistency of a single transaction is the responsibility of the application writer who writes the transaction, and the integrity subsystem of the DBMS performs the test task when the system is running.
3. Isolation (Isolation)
When multiple transactions are executed concurrently, the system should guarantee the same results as those transactions were executed separately, at which point the transaction reached the requirement of isolation. That is, when multiple transactions are executing concurrently, the execution result is guaranteed to be correct, just like a single-user environment.
Isolation is implemented by the concurrency control subsystem of the DBMS.
4. Persistence (Durability)
Once a transaction has completed its entire operation, all its updates to the database should be permanently reflected in the database and not lost. This is true even after a system failure occurs.
Persistence is implemented by the recovery management subsystem of the DBMS.

• Checkpoint Technology

REDO (Redo) and undo (undo) processing are actually implemented using the checkpoint (Checkpoint) method.
Two checkpoints and their intermediate points of failure divide the transaction into five categories:
T1: The transaction has completed before it reaches the first checkpoint.
T2: The transaction was completed before the first checkpoint until the point of failure.
T3: The transaction starts before the first checkpoint until the point of failure is not completed.
T4: The transaction starts after the first checkpoint and is completed before the point of failure.
T5: The transaction starts after the first checkpoint until the point of failure is not completed.
Fault handling:

• Transaction T1 do not have to be restored. Because their updates have been written to the database at the first checkpoint.
• Transaction T2 and transaction T4 must be re-made (REDO). Because they end before the next checkpoint. Their modifications to the DB are still in the memory buffer and are not written to disk.
• Transaction T3 and transaction T5 must be undone (undo). Since they are not yet finished, the changes to the DB that the transaction has made must be undone.

• Database Concurrency Operations Issues

• Missing update issues:
  The initial value of a in the database is 100, the transaction T1 the value of a by 30, and the transaction T2 1 time times more for a. If the order of execution is first T1 after T2, then the value of result a is 140. If T2 is executed first, then the value of a is 170. Both of these situations should be correct. However, the value of the transaction T1 read A is performed in the transaction T2 also reads the value of a, which is overwritten by the value of the transaction update performed after the first execution, and the result is missing the update operation to the database of the transaction performed first. Thus this concurrency operation is not correct.
• Read dirty data problem:
  (1) The initial value of a in the database is 100, and the transaction T1 modifies a to 70, but not yet committed (that is, the commit operation is not done), and the transaction T2 immediately reads the uncommitted a value (70). Then, the transaction T1 does the rollback operation and restores the value of a to 100. The transaction T2 is still using the revoked value of a (70). In database technology, uncommitted data that is subsequently revoked is referred to as "dirty data".
  (2) The initial value of a in the database is 100, and the transaction T1 modifies a to 70, but not yet committed (that is, the commit operation is not done), and the transaction T2 immediately reads the uncommitted a value (70). Transaction T2 increases the value of a by 1 time times, performing a transaction T2 updating the database. Then, the transaction T1 does the rollback operation and restores the value of a to 100. The result is a loss of transaction T2 update operations to the database. The integrity of the database is compromised.
• Non-repeatable READ problem:
  The initial value of a in the database is 100, and the transaction T1 needs to read the same data item a two times, but in the interval of two read operations, another transaction T2 changes the values of a. As a result, T1 read the same data item A at two times, but it reads a different value.

• Blockade technology

A lock is a variable associated with a data item that describes the state of a data item for operations that might be applied to that data item.
Typically, there is a lock for each data item in the database. The purpose of a lock is to enable concurrent transactions to synchronize access to data items in the database. There are two main blocking technologies: Exclusive blockade and shared blockade.

• Exclusive block (X-Lock):
  Exclusive Lock is referred to as x Lock, also known as write lock.
  Definition: If a transaction T implements an X lock on a data R (which can be a data item, a record, a dataset, or an entire database), then no other transaction T is allowed to add any type of lock to the data until T is unblocked against the data R. This type of lock is called an "x lock".
  There are two operations using the X Lock:
  (1) Apply for X-lock operation "Xfind R": Indicates the transaction to the data R application plus x lock, if successful, you can read or write data R; If unsuccessful, the transaction will enter the waiting queue until the X lock is granted, and the transaction can continue.
  (2) Unlock x lock operation "Xrelease R": Represents the X lock that the transaction is going to release to the data R.
  After a transaction has an X lock on the data, and the data is modified, if it is unlocked prematurely, it is possible for other transactions to read uncommitted data (and then be rolled back), causing the loss of updates to other transactions. In order to solve this problem, the X-lock cancellation should be merged into the end of the transaction (commit or rollback) operation. That is, the system does not release the X-lock operation of the statement, in the commit statement and the ROLLBACK statement contains the operation to unlock the X-lock.
• Shared block (S-lock):
  Shared Lock is referred to as S lock, also known as read lock.
  Definition: If a transaction t adds an S lock to a data, it still allows other transactions to add s lock on the data, but never allows any transaction to add an X lock to the data until all s locks on the data are lifted.
  There are three operations using the S lock:
  (1) Apply S lock operation "Sfind R": Indicates the transaction to the data R application Plus s lock, if successful, you can read the data r, but can not write data R; If unsuccessful, the transaction will enter the waiting queue until the S lock is allowed, and the transaction can continue.
  (2) Upgrade and write operation "Updx R": Indicates that the transaction to the data r of the S lock to the X lock, if successful update data r, otherwise the transaction into the waiting queue.
  (3) Unlock s lock operation "Srelease R": Indicates that the transaction is to unlock the S lock on the data R.
  It can be seen that the transaction allowed S lock can only read data, can not update the data, if there is an update, the first to upgrade the S lock to x lock. In addition, since S lock allows only read data, the operation to unlock s lock does not have to be merged into the end operation of the transaction, and the S lock can be lifted at any time as needed.
• Block granularity:
  Definition: The size of the blocked object is called the blocking granularity.
  The blocked object can be either a logical unit or a physical unit. In a database, a blocked object can be a property value, a collection of attribute values, a tuple, a relationship, an index item, an entire index, an entire database, or a logical unit such as a page (a data page or index page), a block, and so on.
  The granularity of the blockade is closely related to the concurrency of the system and the overhead of concurrency control. The greater the granularity of the blockade, the smaller the concurrency, but the smaller the overhead of the system, the smaller the granularity of the blockade, the higher the concurrency, but the greater the overhead of the system.

• Access Mode for transactions

(1) Read Only (read-only): Transactions can only be read operations on the database. When this pattern is defined, the subsequent transactions are read-only.
(2) Read write: The operation of the transaction on the data can be either a read operation or a write operation. When this pattern is defined, the subsequent transactions are read-write. This mode is defaulted at the beginning of the program.

• Isolation level of a transaction

(1) serializeable (serializable): Allows transactions to execute concurrently with other transactions, but the system must ensure that concurrent calls are serializable without errors.
(2) Repeatable READ (Repeatable Read): Only the transaction is allowed to read the submitted data, and other transactions are not allowed to modify this data when the same data is read two times.
(3) Read COMMITTED (read commit data): Allows transactions to read committed data, but does not require "repeatable reads".
(4) Read UNCOMMITTED (can read UNCOMMITTED data): Allows transactions to read committed or uncommitted data.

• Statement definition:
  SET TRANSACTION Isolation Level SERIALIZABLE
  SET TRANSACTION Isolation Level repeatable READ
  SET TRANSACTION Isolation Level READ COMMITTED
  SET TRANSACTION Isolation Level READ UNCOMMITTED

Excerpt from: "Principles of the database"

Database principles-Basic concepts of transactions