MySQL concurrency control and transactions

concurrency control

Size of Lock:

Table-Level Locks

Row-level Locks

Lock:

Read lock: Shared lock, read-only non-writable, multiple read non-blocking,

Write Lock: Exclusive lock , Lock, a write lock will block other read and write locks

Realize

Storage Engine: Implement its own lock policy and lock granularity

Server level: Implement lock, table level lock; user can explicitly request

Classification:

Implicit lock: Auto-apply lock by storage engine

Explicit Lock: User requests manually

Lock strategy: Balancing mechanism in lock granularity and data security seeking

Show use lock

LOCK TABLES

Tbl_name [[as] alias] Lock_type

[Tbl_name [[as] alias] lock_type] ...

Lock_type:read , WRITE

UNLOCK TABLES Unlock

FLUSH TABLES tb_name[,...] [With READ LOCK]

Closes the table that is being opened (clears the query cache), usually with a global read lock before the backup

SELECT clause [For UPDATE | LOCK in SHARE MODE]

Add write or read lock on query

Transaction

Transactions Transactions : A set of atomic SQL statement, or an independent unit of work

transaction log: Record transaction information, implement Undo,redo and other failure recovery functions

ACID Features:

A : atomicity Atomic; All operations in the entire transaction are either successfully executed or rolled back after all failures

C : Consistency consistency; The database is always transitioning from one consistency state to another consistency state

I : Isolation isolation; an action made by a firm cannot be seen by other transactions until it is committed; isolation has multiple isolation levels for concurrency

D : Durability persistent; Once a transaction commits, its modifications are persisted in the database

Example: A Give B transfer, not A deducted the money and B no more money .

Transaction life cycle

Adding and deleting to calculate the transaction, query Select not counted within a transaction

Transaction

To start a transaction: START TRANSACTION

End transaction:

(1) Commit: Submit

(2) ROLLBACK: rollback

Note: Only transactional storage engines can support this type of operation

Recommendation: Explicitly request and commit a transaction instead of using the auto-commit feature

Set [Global] autocommit={1|0}

default is 1 Automatic submission, if you want to permanently save the words to write to the configuration file, autocommit=0

Transaction support SavePoint: savepoint

SavePoint identifier

ROLLBACK [Work] to [savepoint] Identifier

RELEASE savepoint Identifier

Transaction ISOLATION LEVEL

Transaction ISOLATION Level: more stringent from top to bottom

? READ Uncommitted can read to uncommitted data, resulting in dirty reads

? READ COMMITTED read-committed data, but uncommitted data is unreadable, resulting in non-repeatable reads that can be read to multiple commit data, resulting in inconsistent data per read

? Repeatable READ REPEATABLE Read, multiple read data are consistent, generating phantom read, that is, even if there are other committed transaction modification data, still can only read the old data before the modification. This is the MySQL default setting

? Serializabile Serializable , uncommitted read transactions block modify transactions, or uncommitted modification transactions block read transactions. Cause poor concurrency performance

MVCC: Multi-version concurrency control, and transaction-level correlation

Transaction ISOLATION LEVEL

Specify the transaction ISOLATION level:

Server Variables tx_isolation specified, the default is Repeatable-read , can be in GLOBAL and the SESSION level to set

SET tx_isolation= "

read-uncommitted

read-committed

Repeatable-read

SERIALIZABLE

specified in the server option

Vim/etc/my.cnf

[Mysqld]

transaction-isolation=serializable Note: The configuration name is not the same as the variable name

concurrency control for transactions

Deadlock:

Two or more transactions occupy each other in the same resource and request a lock on the state of the resource occupied by the other

Example: TransactionTR1Locktable1 Row1, and then the transactionTransaction2Locktable2 Row2, the two do not interfere, but if at this timeTR1try to modifytable2 Row2it will get stuck, at this point ifTR2then try to modifytable1 Row1, youTR1and theTR2It's going to get stuck, and a deadlock happens.

MySQL automatically detects deadlocks and sacrifices one of the transactions to unlock deadlocks, usually at the expense of waiting time.

Transaction log:

The write type for the transaction log is "append", so its action is "order IO "; often also known as: write-ahead log Write ahead Logging

log file: Ib_logfile0 , Ib_logfile1

MySQL concurrency control and transactions