Transaction control and locking statements

MySQL supports table-level locking of tables for the MyISAM and memory storage engines, row-level locking of tables in the BDB storage engine, and row-level locking of tables InnoDB the storage engine. By default, table and row locks are automatically obtained without additional commands, but in some cases it is necessary to explicitly lock the table or control the transaction to ensure the integrity of the entire transaction, which requires the use of transaction control and locking statements to complete.

One, lock table and unlock table

Lock tables can lock tables for the current thread. If the table is locked by another thread, the current thread waits, knowing that all locks can be acquired.

Unlock table can free any locks that are obtained by the current thread. When the current thread executes another lock table, or when the connection to the server is closed, all the tables locked by the current thread are implicitly unlocked.

Here's an example: Table T16 gets the read lock, the other session updates the table record waits for the lock, and after the T16 table releases the lock, other sessions can be updated, where Session1 and Session2 represent two simultaneous sessions. Each row in the table represents the health of two sessions at the same time.

Mysql> CREATE TABLE t16 (ID int (one), name varchar (), age int (3));
Query OK, 0 rows affected (0.31 sec)

mysql> desc t16;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int (one)     | YES  |     | NULL    | | |
name  | varchar (63) | YES  |     | NULL    |       | | age   | int (3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in Set (0.00 sec)

mysql> inserts into T16 (ID , name,age) VALUES (1, ' fzy1 ', 1), (1, ' fzy1 ', 1), (3, ' Fzy3 ', 3);
Query OK, 3 row affected (0.13 sec)

Get table lock and release lock case

session_1	session_2
Get the read lock for table t16 mysql> lock table t16 read; Query OK, 0 rows Affected (0.00 sec)
When session can query the table record Mysql> SELECT * from T16 where id=1; +------+------+------+ | ID | name | Age | +------+------+------+ | 1 | fzy1 | 1 | +------+------+------+ 1 row in Set (0.00 sec)	Other sessions can also query the table's records Mysql> SELECT * from T16 where id=1; +------+------+------+ | ID | name | Age | +------+------+------+ | 1 | fzy1 | 1 | +------+------+------+ 1 row in Set (0.00 sec)
	Other session update locked table waits for lock Database changed mysql> update t16 set age=3 where id=1; (Always blinking at the cursor, that is, waiting state)
Release lock mysql> unlock tables; Query OK, 0 rows Affected (0.00 sec)	Wait
	Session get lock, update operation complete Query OK, 1 row affected (1 min 38.38 sec) Rows matched:1 changed:1 warnings:0

Second, transaction control

MySQL supports local transactions through set autocommit, start transaction, Commit, rollback, and so on. By default, MySQL is autocommit (autocommit), and if explicit commits and rollback are required to commit and rollback transactions, it is necessary to start the transaction with explicit transaction control commands, which is clearly different from Oracle's transaction management.

(1) Start transaction or BEGIN statement can begin a new transaction

(2) Commit and rollback are used to commit or roll back the transaction.

(3) Chain and release words are used to define actions that occur after a transaction commits or rolls back, chain immediately starts a new transaction and has the same isolation level as the transaction just now, and release disconnects the client.

(4) Set autocommit can modify the way the current connection is submitted, and if set autocommit=0 is in place, all transaction reads after the setting need to be committed or rolled back by explicit commands.

If you only need transaction control for some of the statements, it is convenient to start a transaction using the START TRANSACTION statement, so that you can automatically return to autocommit after the transaction has ended, if you want all transactions to be not committed automatically, Then it is easier to control the transaction by modifying the autocommit.

Below, we demonstrate a way to start a transaction using start transcation automatically back to autocommit after committing. If you use commit and chain at the time of submission, a new transaction begins immediately after the commit.

Start Transation and commit and chain cases

session_1	session_2
Query id=4 from table t16, resulting in null Mysql> SELECT * from T16 where id=4; Empty Set (0.00 sec)	Query id=4 from table t16, resulting in null Mysql> SELECT * from T16 where id=4; Empty Set (0.00 sec)
Start a transaction with the start rransaction command, insert a record into the table t16, no commit mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) mysql> insert INTO t16 (id,name,age) VALUES (4, ' Fzy4 ', 4); Query OK, 1 row affected (0.01 sec)
Query id=4, there are results Mysql> SELECT * from T16 where id=4; +------+------+------+ | ID | name | Age | +------+------+------+ | 4 | Fzy4 | 4 | +------+------+------+ 1 row in Set (0.00 sec)	Query table T16 id=4, the result is still empty Mysql> SELECT * from T16 where id=4; Empty Set (0.00 sec)
Performing a COMMIT Transaction Mysql> commit; Query OK, 0 rows affected (0.04 sec)
	Query again Mysql> SELECT * from T16 where id=4; +------+------+------+ | ID | name | Age | +------+------+------+ | 4 | Fzy4 | 4 | +------+------+------+ 1 row in Set (0.00 sec)
This transaction is executed in accordance with AUTOCOMMIT mysql> INSERT INTO t16 (id,name,age) VALUES (5, ' Fzy5 ', 5); Query OK, 1 row affected (0.04 sec)
	Records inserted by a query Mysql> SELECT * from T16 where id=5; +------+------+------+ | ID | name | Age | +------+------+------+ | 5 | Fzy5 | 5 | +------+------+------+ 1 row in Set (0.01 sec)
Start a transaction again with the start transaction mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) # #插入语句 mysql> insert INTO t16 (id,name,age) VALUES (6, ' Fzy6 ', 6); Query OK, 1 row Affected (0.00 sec) # #提交并立即开启一个新事务 (and just have the same isolation level) mysql> commit and chain; Query OK, 0 rows affected (0.04 sec) # #插入数据 mysql> insert INTO t16 (id,name,age) VALUES (7, ' Fzy7 ', 7); Query OK, 1 row Affected (0.00 sec)
	Inquire Mysql> select * from T16; +------+------+------+ | ID | name | Age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | Fzy2 | 2 | | 3 | Fzy3 | 3 | | 4 | Fzy4 | 4 | | 5 | Fzy5 | 5 | | 6 | Fzy6 | 6 | +------+------+------+ 6 rows in Set (0.00 sec) Found Id=7 didn't have this record.
Re-submit the transaction Mysql> commit; Query OK, 0 rows affected (0.05 sec)
	Inquire Mysql> select * from T16; +------+------+------+ | ID | name | Age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | Fzy2 | 2 | | 3 | Fzy3 | 3 | | 4 | Fzy4 | 4 | | 5 | Fzy5 | 5 | | 6 | Fzy6 | 6 | | 7 | Fzy7 | 7 | +------+------+------+ 7 rows in Set (0.01 sec) Id=7 This record has been inserted

Starting a new transaction with the start transaction command during the lock table results in an implied unlock tables being executed

Unlock tables caused by Start transaction

session_1	session_2
Query id=9 records from table t16, resulting in null Mysql> SELECT * from T16 where id=9; Empty Set (0.00 sec)	Query id=9 records from table t16, resulting in null Mysql> SELECT * from T16 where id=9; Empty Set (0.00 sec)
Write lock on table t16 mysql> lock table T16 write; Query OK, 0 rows Affected (0.00 sec)
	Read operation on table T16 blocked Mysql> select * from T16; Wait
Insert a record mysql> INSERT INTO t16 (id,name,age) VALUES (9, ' Fzy9 ', 9); Query OK, 1 row affected (0.06 sec)	Wait
Roll back just record mysql> rollback; Query OK, 0 rows Affected (0.00 sec)	Wait
Start a transaction with the start transaction command mysql> start transaction; Query OK, 0 rows Affected (0.00 sec)	Wait
	Session1 start a transaction, the table lock is released and can be queried Mysql> select * from T16; +------+------+------+ | ID | name | Age | +------+------+------+ | 1 | fzy1 | 3 | | 2 | Fzy2 | 2 | | 3 | Fzy3 | 3 | | 4 | Fzy4 | 4 | | 5 | Fzy5 | 5 | | 6 | Fzy6 | 6 | | 7 | Fzy7 | 7 | | 8 | fzy8 | 8 | | 9 | Fzy9 | 9 | +------+------+------+ 9 rows in Set (0.00 sec) Table lock added to lock, cannot be rolled back through rollback

Therefore, in the same transaction, it is best not to use a different storage engine, otherwise the rollback type table should be handled in a special way. Because, commit, rollback can only commit and rollback to a table of transaction types.

Typically, only committed transactions are logged to a binary log, however, if a transaction contains a table that is not of a transaction type, the rollback operation is also recorded in the binary log to ensure that updates to the non-transactional type table can be replicated to the database (slave).

NOTE: All DDL statements cannot be rolled back, and some DDL statements result in implicit commits.

In a transaction, you can specify a ROLLBACK TRANSACTION section by defining SavePoint, but you cannot specify a part of the commit transaction. For complex applications, you can define several different savepoint to meet different conditions, roll back different savepoint, and be aware that if you define a savepoint with the same name, the savepoint that you define later will overwrite the previous definition. For savepoint that you do not need to use, you can remove them by using the release SavePoint command.

Here we simulate rollback transactions:

Simulate ROLLBACK TRANSACTION

session_1	session_2
Query id=10 from table t16, empty Mysql> SELECT * from T16 where id=10; Empty Set (0.00 sec)	Query id=10 from table t16, empty Mysql> SELECT * from T16 where id=10; Empty Set (0.00 sec)
Start a transaction, insert data into table id=10 mysql> start transaction; Query OK, 0 rows Affected (0.00 sec) mysql> insert INTO t16 (id,name,age) VALUES (Ten, ' Fzy10 ', ten); Query OK, 1 row Affected (0.00 sec)
You can find the inserted data. Mysql> SELECT * from T16 where id=10; +------+-------+------+ | ID | name | age | +------+-------+------+ | 10 | Fzy10 | Ten | +------+-------+------+ 1 row in Set (0.00 sec)	Unable to find the inserted data Mysql> SELECT * from T16 where id=10; Empty Set (0.00 sec)
Define SavePoint, the name is Test_savepoint1, and then insert a piece of data Mysql> savepoint Test_savepoint; Query OK, 0 rows Affected (0.00 sec) mysql> insert INTO t16 (id,name,age) VALUES (one, ' fzy11 ', one); Query OK, 1 row Affected (0.00 sec)
can query to insert 2 records Mysql> SELECT * from T16 where id=11; +------+-------+------+ | ID | name | age | +------+-------+------+ | 11 | Fzy11 | One | +------+-------+------+ 1 row in Set (0.00 sec	Still can't query to insert 2 records Mysql> SELECT * from t16 where id=10 or id=11; Empty Set (0.00 sec)
Roll back the savepoint you just defined mysql> rollback to savepoint test_savepoint; Query OK, 0 rows Affected (0.00 sec)
Only the first record can be queried from the table Mysql> SELECT * from t16 where id=11 or id=10; +------+-------+------+ | ID | name | age | +------+-------+------+ | 10 | Fzy10 | Ten | +------+-------+------+ 1 row in Set (0.00 sec)	You still can't query to the record you just inserted Mysql> SELECT * from t16 where id=10 or id=11; Empty Set (0.00 sec)
Submit Mysql> commit; Query OK, 0 rows affected (0.05 sec)
We can only find one record. Mysql> SELECT * from t16 where id=11 or id=10; +------+-------+------+ | ID | name | age | +------+-------+------+ | 10 | Fzy10 | Ten | +------+-------+------+ 1 row in Set (0.00 sec)	We can find a record. Mysql> SELECT * from t16 where id=11 or id=10; +------+-------+------+ | ID | name | age | +------+-------+------+ | 10 | Fzy10 | Ten | +------+-------+------+ 1 row in Set (0.00 sec)

Third, the use of distributed transactions (XA)

The current distributed transaction only supports the InnoDB storage engine, a distributed transaction involves multiple actions, which are transactional in nature, all actions must be completed successfully together, or rolled together.

1, distributed Transaction principle

In MySQL, applications that use distributed transactions involve one or more resource managers and one transaction manager.

(1) the Resource Manager (RM) is used to provide access to transactional resources. The database server is a resource manager that must be able to commit or rollback transactions managed by RM. such as: multiple MySQL database as a multiple explorer or several MySQL server and several Oracle servers as a resource manager.

(2) The transaction Manager (TM) is used to reconcile transactions as part of a distributed transaction. TM communicates with RM S, which manages each transaction. In a distributed transaction, each individual transaction is a "branch transaction" of a distributed transaction. Distributed transactions and each branch are marked by a named method.

When MySQL performs xa, the MySQL server acts as a resource manager for managing XA transactions in a distributed transaction. The client connected to the MySQL server corresponds to the transaction manager

To perform a distributed transaction, you must know which resource managers are involved in the distributed transaction and that each resource manager's transaction is executed to a transaction that can be committed or rolled back. These branch transactions must be committed or rolled back as an atomic operation, based on the internal context of the execution reported by each resource manager. To manage a distributed transaction, you must consider any component or connection network that may fail.

The process used to perform a distributed transaction uses two-phase commit, which occurs after the actions required by each branch of the distributed transaction have been executed.

(1) in the first stage, all the branches are ready, i.e. they are told by TM to be ready to submit. Typically, this means that each RM used to manage the branch records the actions of the branch that is being saved stably. The branch indicates whether they can do so. These results are used in the second phase.

(2) In the second phase, TM tells RM s whether to commit or rollback, and if all branches indicate that they will be able to submit when the branch is ready, then all the branches are informed to submit. If in preparation Yes, any branch indicates that he will not be able to commit, then all branches are told to roll back.