Properties of database Transactions-acid

Properties of database Transactions-acid

1, database Transaction Properties-acid (the first letter of four English words):

1) Atomic sex (atomicity)

So-called atomicity is the operation of a group of operations as an atomic operation, that is either all executed or not.

2) Consistency (consistency)

The consistency of a transaction means that the database must be in a consistent state before and after a transaction is executed. If the transaction completes successfully, all changes in the system are applied correctly and the system is in a valid state. If an error occurs in a transaction, all changes in the system are automatically rolled back and the system returns to its original state.

3) Isolation (isolation)

Isolation refers to concurrent transactions that are isolated from each other. That is, the operations within a transaction and the data being manipulated must be blocked from being seen by other transactions attempting to modify it.

4) Persistence (durability)

Persistence means that once a transaction is committed, its changes to the data in the database are permanent, and subsequent operations and database failures should not have any effect on it. That is, once a transaction commits, the DBMS (database Management System) guarantees that its changes to the data in the database should be permanent, and that the persistence is guaranteed by database backup and recovery.

2, in the relational database, the separation of the transaction is divided into four isolation levels, before interpreting these four levels, first introduces several concepts of reading data.

1) Dirty Read (Dirty reads): The so-called dirty reading is the Dirty data (drity) read, and dirty data refers to the uncommitted data. In other words, a transaction is making changes to a record, before this transaction is completed and submitted, the data is pending (possibly committed or rolled back), at which point the second transaction reads the uncommitted data and makes further processing, resulting in uncommitted data dependency relationships. This phenomenon is known as dirty reading.

2 not REPEATABLE read (non-repeatable reads): A transaction reads the same record successively, but the data read two times is different, we call it not repeatable read. In other words, this transaction is modified by other transactions between two read times.

3) Phantom Read (Phantom reads): A transaction reads the previously retrieved data by the same query criteria, but it finds that other transactions have inserted new data that satisfies its query criteria, a phenomenon called Phantom reading.

3, the transaction four isolation level comparison:

1 uncommitted read (READ UNCOMMITTED): The SELECT statement is executed in a non locking manner, so it is possible to read dirty data with the lowest isolation level.

2 Commit read (Read Committed): Only data that has been submitted can be read. Dirty reads are resolved, but not repeatable read.

3) Repeatable Read repeated read: Queries within the same transaction are all at the beginning of the transaction and the InnoDB default level. In the SQL standard, this isolation level eliminates the need for repeatable reads, but there are also phantom reads.

4 serial Read (Serializable): Full serialization read, all SELECT statements are implicitly converted to select ... Lock in SHARE MODE, where reading uses table-level shared locks, both reads and writes are blocked. Highest isolation level.

Isolation Level Comparison table:

4, Transaction Isolation level settings:

1 The service startup option--transaction-isolation or set in the configuration file:

[Mysqld]

transaction-isolation = {read-uncommitted | read-committed

                         | Repeatable-read | SERIALIZABLE}

2 after the service startup settings:

SET [GLOBAL | Session] TRANSACTION Isolation level

{READ Uncommitted | READ Committed | Repeatable READ | SERIALIZABLE}