Understanding and collation of database transactions, isolation levels, locks

Acid Characteristics of database transactions 1. Four characteristics of a transaction

A database transaction (Transaction) is a series of operations performed as a single logical unit of work, either completely or completely without execution. On the one hand, when multiple applications access the database concurrently, transactions can provide an isolation method between applications to prevent interference with each other. Transactions, on the other hand, provide a way for a database operation sequence to recover from failure.

Transactions have four properties: atomicity (atomicity), consistency (consistency), isolated (isolation), persistence (durability), or acid.

1.1 atomicity (Atomicity)

The atomicity of a transaction is that the operations in the transaction are not split and are only allowed to execute all or not.

1.2 Consistency (consistency)

Transactional consistency means that the execution of a transaction does not compromise the consistency of the database, and consistency is also known as completeness. After a transaction executes, the database must transition from one consistent state to another.

1.3 Isolation Type (isolation)

The isolation of transactions means that concurrent transactions are isolated from each other and cannot interfere with each other.

1.4 Persistence (Durability)

The persistence of a transaction means that once a transaction is committed, the state change to the data should be persisted.

Database transaction Isolation LEVEL Source Link: Database transaction isolation LEVEL

There are 4 isolation levels for database transactions, from low to high, READ UNCOMMITTED,Read Committed,Repeatable read, andSerializable, which can be resolved individually by each of the four levels Problems such as dirty reading, non-repetition reading, and Phantom reading.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrency, so the next explanation is around the transaction concurrency.

READ UNCOMMITTED not submitted

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to READ UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level.

Read Committed reading Commit

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to Read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level.

REPEATABLE READ repeat

you can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have Phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Note: The default isolation level for MySQL is repeatable read.

Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

Additional reference Links: MySQL database transaction isolation levels (Transaction isolation level)

Lock (means of concurrency control)

Exclusive locks (exclusive locks) allow only one transaction to access the data

Shared locks allow other transactions to continue using locked resources

Update lock

A lock is the protection of a specified resource, not operations by another transaction, and the locked resources include rows, pages, clusters, tables, and databases. To minimize the cost of the lock, SQL Server automatically locks the resource object with the corresponding level of lock on the task. Locking smaller objects, such as locking rows, can improve concurrency, but it is expensive because if many rows are locked, more locks are required. Locking large objects, such as locking tables, can greatly reduce concurrency because locking the entire table restricts other transactions from accessing other parts of the table, but costs are lower because only a smaller number of locks are maintained.

Set transaction levels: Set TRANSACTION isolation Level

Start transaction: BEGIN Tran

Commit TRANSACTION: Commit

ROLLBACK TRANSACTION: ROLLBACK

Create transaction savepoint: Save TRANSACTION Savepoint_name

Rollback to Transaction point: ROLLBACK TRANSACTION savepoint_name

About database transactions, isolation levels, lock understanding and grooming (go)