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