Database transactions and concurrency control

First, Introduction

A transaction (database Transaction) is a series of operations performed as a single logical unit of work in a database system, as compared to databases. Transactions are independent of each other. Transactions are primarily used in database systems for:

1. Provide reliable logic work units to ensure database recoverability (atomicity), and database consistency.

2. Ensure that database access between concurrent programs is isolated.

By definition: A database transaction must have: atomicity (atomic), consistency (consistent), isolated (isolated), and persistence (durable). DBAs often referred to it as acid.

Atomicity guarantees that the execution of a database or other data store transaction has a "all-or-nothing" nature, i.e. either complete execution or no execution. Isolation guarantees that execution between different transactions is irrelevant. Consistency guarantee results conform to database storage constraints: unique,reference (foreign key), primary key (primary key). Persistence guarantees the persistence of data storage.

Second, the database constraint conditions

1. Primary key (primary key): Identifies the key of the data tuple, must satisfy the value is not NULL, there is only one primary key in a table.

2. Special key (Unique key): Refers to the 0~n (number of n< tuple properties) property set in the database tuple to ensure that the property value is unique in the database table. The default is a unique index in the database index. The value can be null

3. Foreign key (reference key): Used to identify a unique tuple in the referenced table (parent table). The value is null or is a candidate key for the referenced table (the parent table) (multiple primary keys).

Plus: The special key set is the candidate key for the data tuple, which is selected from the candidate key as the data tuple primary key. Tuples from different tables are indexed with foreign keys.

Key Name	Interpretation
Simple (Easy key)	A key that consists of a property in a tuple. Example: (Simple key)
Concatenated (series key)	A single key that is concatenated by at least two attributes (simple keys) in a tuple. Example: (Simple Key_simple key)
Compound (mixed key)	A key that is mixed by at least two attributes (simple keys) in a tuple. Example: (Simple Key,simple key)
Composite (key combination)	A key that is composed of at least 1 mixed keys and at least one simple key in a tuple. Example: (Simple Key,simple key), simple key)
Natural (Natural key)	Generate a key without using the tuple attribute in this database, directly from the outside to define the build.
Surrogate (surrogate key)	The key generated by the database system or, when generated, depends on the generation of the partial candidate key. Many are digital IDs, which are mainly used to improve operational efficiency.
Candidate (candidate key)	Candidate Keys
Primary (primary key)	Use this key in the system implementation to query the data table and to connect between the different tables
Alternate (optional key)	The key that can be used to identify the tuple except the primary key. A key that can be used to query tuples in a single table except for the primary key.
Foreign (foreign key)	The special keys (their values are the same) for the other table in the same database referenced in this sheet.

Third, transaction-type database

A transactional database is a database management system that supports rollback when write transactions in the database do not complete. Most of today's relational database management systems support transactions.

In a database system, a transaction can contain more than one data operation (read, write). Users of the database system are used to ensure data consistency and integrity as a metric for the database. The general paradigm for database transaction Execution (SQL) is:

1. Execution of the transaction (Begin the transaction).

2. Perform a series of data operations (read and write) that belong to a transaction.

3. Commits and terminates the transaction normally if the transaction execution process does not encounter an error.

4. If an error is encountered, rollback the transaction and terminate the transaction.

Implemented, multi-user databases store and process transactions, often by assigning an ID to each transaction.

Other implementation methods:

Nested transactions: A transaction nested within an existing transaction scope. There can be different implementations, but what they have in common is the isolation between unrelated transactions before the outermost transaction is ended, which means that nested transactions do not have to be persisted in updating the data into the database.

Iv. concurrency control

Generalized concurrency control is used to provide rules, design theories and methods to ensure data consistency (consistency) and correctness (correctness) of concurrent operations during different build interactions, and even ensure the consistency and correctness of the whole system. The introduction of concurrency control in the system can reduce the efficiency of the whole system, so the concurrency control is designed to ensure the consistency and correctness of the operation, as well as to improve the running efficiency of the program.

Concurrency control ensures that transactions are run in the database management system without destroying the integrity constraints of the data. Theory, serialization theory (serializability theory) and Atomic transactions (Atomic transction 1993). The serialization theory is used in the database implementation. To ensure the correctness of the data, the DBMS guarantees that only serialized transactions will be executed.

1. Why do I need concurrency control?

If the execution of a transaction is a serialized, sequential execution without a time overlay, then there is no concurrency for the transaction. However, in order to improve the operating efficiency of the system, it allows concurrent operations to cross-read and write to the database, which results in the uncontrolled operation of Read and write operations and the occurrence of some undesirable results. Like what:

I. Missing update issue: On a time series, the second write data transaction executes after the first write data transaction. Example: If 3 transactions (T1,T2,T3) are concurrent, two write, one read. Sequential execution for (T1 write, T2 read, T3 write), because the concurrency situation can not guarantee the order of transactions in time, has appeared (T1 write, T3 write, T2 read) situation. More out of the current database data update situation.

II, read dirty data problem: On a time series, the second read operation is performed before the first delete operation. Example: Adding 2 transactions (t1,t2) concurrency, one delete, one read. Sequential execution for (T1 delete, T2 read), in case of concurrency: (T2 read, T1 deleted)

III, incorrect summary of the problem: two transaction concurrency, one transaction is used to summarize how many tuples of the same attribute value in the database, another transaction writes the data, the write operation can be written before the summary is completed, or it may be written after the summary. This results in a summary of the data being a random value.

2. Concurrency control mechanism

Optimistic lock: After the transaction is executed, the isolation and integrity of the transaction concurrency are checked, and if the constraints of the transaction are not met, then some transactions that have previously been abandoned are picked up and re-executed. Application: The amount of transactions performed repeatedly is small.

Pessimistic lock: If the execution of a transaction does not meet the conditions of concurrency control (isolation and integrity constraints), the lock is released until the transaction satisfies the concurrency control condition.

Semi-optimistic Lock: one of the conditions of concurrency control plus pessimistic lock, another part of the condition plus optimistic lock.

When two transactions between each other locking (between each other, one to the other locking) easily trigger a deadlock. The deadlock problem is resolved by opening a resident transaction to unlock the deadlock and quickly re-open and re-execute.

2.1 Locks (e.g. two-stage lock-two Phase Locking 2PL) Lock the data on the transaction that controls the write, and another transaction that locks on one transaction may also be locked.

2.2 Serialization Graph check. Detects whether a transaction call graph constitutes a ring, which is a deadlock caused by discarding certain transactions to release a dead loop.

2.3 is sorted by timestamp. Assigns a timestamp to the transaction and sorts the resource by timestamp to the transaction in which the data is concurrent.

2.5 According to the order of submission.

2.6 Multi-version concurrency control.

V. References:

http://en.wikipedia.org/w/index.php?title=Database_transaction&oldid=521605403

Http://en.wikipedia.org/wiki/Unique_key

Http://en.wikipedia.org/wiki/Foreign_key

Http://en.wikipedia.org/wiki/Nested_transaction

Http://en.wikipedia.org/wiki/Concurrency_control

Database transactions and concurrency control