MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control

1. Concurrent access control

The concurrent access control technology implemented is based on locks;

Locks are divided into table-level locks and row-level locks. The MyISAM storage engine does not support row-level locks; InnoDB supports table-level locks and row-level locks.

Locks are classified into read locks and write locks. Read locks are also called shared locks. When a read lock is added, other people can read. Write locks are also called exclusive locks or exclusive locks. A write lock will block other read and write operations.

Locks are divided into implicit locks and explicit locks. Implicit locks are managed by the storage engine, while explicit locks are manually added by users.

Lock strategy: A balance mechanism between lock granularity and data security.

How to use explicit locks: LOCK TABLES tbl_name READ|WRITE

MariaDB [school]> LOCK TABLES students READ; #Add read lock

MariaDB [school]> UNLOCK TABLES; #Unlock

Read lock: No one can write

Write lock: You can read and write, but others cannot read and write

FLUSH TABLES tb_name : Close the open table (clear the query cache), usually add a global read lock before the backup

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE] adds write or read locks during query

2. Transactions

A set of atomic SQL statements, or an independent unit of work

1. Transactions follow the ACID principle:

• A: atomicity; all operations in the entire transaction are either successfully executed or rolled back after all failures.
• C: consistency; the database always transitions from one consistent state to another
• I: Isolation; operations performed by a transaction cannot be seen by other transactions before they are committed; there are multiple levels of isolation to achieve concurrency
• D: durability; once a transaction is committed, the changes made will be permanently saved in the database

2. Transaction life cycle

Explicit transactions: clearly specify the start of a transaction

Implicit transaction: The default is implicit transaction, which is committed directly after each statement is executed.

autocommit = {OFF|ON} turns autocommit on or off. It is recommended to explicitly request and commit transactions instead of using the "autocommit" function.

Start a transaction: START TRANSACTION;

Insert tag: ROLLBACK TO ##;

Rollback to the specified tag: ROLLBACK TO ##;

Undo all: ROLLBACK;

Commit the transaction: COMMIT；

Delete tag: RELEASE SAVEPOINT;

MariaDB [school]> START TRANSACTION; #Explicitly specify to start a transactionMariaDB [school]> INSERT students(StuID,Name,Age,Gender) VALUES (26,'Tom',22,'M'); #Add a recordMariaDB [school]> SAVEPOINT sp26; #Insert a labelMariaDB [school]> INSERT students(StuID,Name,Age,Gender) VALUES (27,'Maria',12,'F'); #Add another recordMariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27); #Check and you can see the data just inserted+-------+-------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------+-----+--------+---------+-----------+
| 26 | Tom | 22 | M | NULL | NULL |
| 27 | Maria | 12 | F | NULL | NULL |
+-------+-------+-----+--------+---------+-----------+
MariaDB [school]> ROLLBACK TO sp26; #Revoke to the state before sp26 tag MariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27); #Check, maria's information has been withdrawn+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
| 26 | Tom | 22 | M | NULL | NULL |
+-------+------+-----+--------+---------+-----------+
MariaDB [school]> COMMIT; #Commit transaction MariaDB [school]> SELECT * FROM students WHERE stuid IN (26,27); #Final data+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
| 26 | Tom | 22 | M | NULL | NULL |
+-------+------+-----+--------+---------+-----------+

3. Transaction isolation level

• READ UNCOMMITTED Other transactions can see uncommitted dirty data, resulting in dirty reads
• READ COMMITTED After the transaction is committed, other transactions can see the modified data. The data read each time may be inconsistent and cannot be read repeatedly.
• REPEATABLE READ Repeatable read, the data seen each time is consistent, the latest data cannot be seen after the data is modified, and phantom read will occur (default setting)
• SETIALIZABILE Uncommitted read transactions block modification transactions, serial execution, poor concurrency

MVCC: Multi-version concurrency control, related to transaction level

Modify the transaction isolation level: server variable tx_isolation specifies, defaults to REPEATABLE-READ, can be set at GLOBAL and SESSION levels

tx_isolation

• Description: The transaction isolation level. See also SET TRANSACTION ISOLATION LEVEL.
• Commandline: --transaction-isolation=name
• Scope: Global, Session
• Dynamic: Yes
• Type: enumeration
• Default Value: REPEATABLE-READ
• Valid Values: READ-UNCOMMITTED , READ-COMMITTED , REPEATABLE-READ , SERIALIZABLE

MariaDB [school]> SELECT @@tx_isolation; #Default is repeatable read level+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
MariaDB [school]> SET tx_isolation='READ-UNCOMMITTED';
MariaDB [school]> set tx_isolation='READ-COMMITTED';
MariaDB [school]> set tx_isolation='REPEATABLE-READ';
MariaDB [school]> set tx_isolation='SERIALIZABLE';

4. Deadlock

​Two or more transactions occupy the same resource and request to lock the resource occupied by each other, which will cause a deadlock.

When transaction A modifies the third row of table t1 and transaction B modifies the second row of table t2, transaction A is blocked when it modifies the second row of table t2. Then transaction B is blocked when it modifies the third row of table t1, and a deadlock occurs.

Two transactions try to change each other's modified tables at the same time, blocking each other; the system will detect the deadlock and automatically sacrifice a low-cost transaction to resolve the deadlock.

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

View the process list: MariaDB [school]> SHOW PROCESSLIST;

Kill the process: MariaDB [school]> KILL 5;

This concludes the article on MySQL Series 10: MySQL Transaction Isolation to Implement Concurrency Control. For more information on MySQL concurrency control, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

This concludes the article on MySQL Series 10: MySQL Transaction Isolation to Implement Concurrency Control. For more information on MySQL concurrency control, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!