Shulou(Shulou.com)05/31 Report--

This article introduces to you whether the SET TRANSACTION in MySQL will affect the transaction, the content is very detailed, interested friends can refer to, hope to be helpful to you.

MySQL supports all transaction isolation levels in the SQL:1992 standard, using SET TRANSACTION to set different transaction isolation levels or access modes.

As we all know, among the built-in engines of MySQL, only InnoDB and NDB support transactions, and InnoDB engine supports transactions most comprehensively and widely, so the discussion in this paper is based on InnoDB engine, and the tables used in the experiment are based on InnoDB tables.

FeatureMyISAMMemoryInnoDBArchiveNDBTransactionsNoNoYesNoYes

SET TRANSACTION can be used in MySQL to influence transaction characteristics, and this statement can specify one or more comma-separated lists of eigenvalues, each of which sets the transaction isolation level or access mode. The complete syntax of this statement in MySQL 5.7

SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic]... transaction_characteristic: {ISOLATION LEVEL level | access_mode} level: {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE} access_mode: {READ WRITE | READ ONLY}

The grammar is simple and clear, and there are several key concepts that need to be understood.

Transaction Isolation Levels (transaction isolation level)

Transaction isolation is the basic capability of the database. I in ACID refers to transaction isolation. Generally speaking, when multiple users access the database concurrently, the transaction opened by the database for each user cannot be disturbed by the operation data of other transactions, and multiple concurrent transactions should be isolated from each other.

So what exactly does it mean to be isolated from each other? The SQL:1992 standard specifies four transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

InnoDB supports all four isolation levels, and the default level is REPEATABLE READ.

Root@database-one 07:43: [(none)] > select @ @ tx_isolation;+-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (sec)

Create a new session for verification, and the default isolation level of the session is indeed REPEATABLE-READ.

InnoDB implements each transaction isolation level through different locking strategies, and the higher the isolation level, the higher the lock cost. Let's look at the differences at different levels through examples.

Root@database-one 08:38: [gftest] > create table testtx (name varchar (10), money decimal (10dag2)) engine=innodb;Query OK, 0 rows affected (0.12 sec) root@database-one 08:42: [gftest] > insert into testtx values ('Amur sec 6000), Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0root@database-one 08:43: [gftest] > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

The table testtx is created above, and three pieces of data are inserted, indicating that A has 6000 yuan, B has 8000 yuan, and C has 9000 yuan.

REPEATABLE READ, the consistent reads within the same transaction reads the snapshot established by the first read. This means that if multiple normal (non-locked) SELECT statements are issued in the same transaction, the data found by these SELECT statements remains consistent.

Create session 1 and turn off MySQL's default transaction autocommit mode (for more information, please refer to the transaction control statement in MySQL).

Root@database-one 08:58: [(none)] > prompt\ u@database-one\ R:\ m:\ s [\ d] session1 > PROMPT set to'\ u@database-one\ R:\ m:\ s [\ d] session1 > 'root@database-one 08:58:41 [(none)] session1 > use gftest;Database changedroot@database-one 08:58:55 [gftest] session1 > SET autocommit=0;Query OK, 0 rows affected (sec) root@database-one 08:59:21 [gftest] session1 > show variables like' autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.02 sec) root@database-one 08:59:36 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Create session 2 and turn off MySQL's default transaction autocommit mode (for more information, please refer to the transaction control statement in MySQL).

Root@database-one 09:01: [(none)] > prompt\ u@database-one\ R:\ m:\ s [\ d] session2 > PROMPT set to'\ u@database-one\ R:\ m:\ s [\ d] session2 > 'root@database-one 09:02:13 [(none)] session2 > use gftest;Database changedroot@database-one 09:02:24 [gftest] session2 > SET autocommit=0;Query OK, 0 rows affected (sec) root@database-one 09:02:30 [gftest] session2 > show variables like' autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.00 sec) root@database-one 09:02:37 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Create session 3 and turn off MySQL's default transaction auto-commit mode (for more information, please refer to the transaction control statement in MySQL).

Root@database-one 09:03: [(none)] > prompt\ u@database-one\ R:\ m:\ s [\ d] session3 > PROMPT set to'\ u@database-one\ R:\ m:\ s [\ d] session3 > 'root@database-one 09:03:44 [(none)] session3 > use gftest;Database changedroot@database-one 09:03:47 [gftest] session3 > SET autocommit=0;Query OK, 0 rows affected (sec) root@database-one 09:03:56 [gftest] session3 > show variables like' autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | OFF | +-+-+ 1 row in set (0.01sec) root@database-one 09:04:04 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

A transfer 100 yuan to B. Simulate in session1.

Root@database-one 09:06:03 [gftest] session1 > update testtx set money=money-100 where name='A';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 09:07:34 [gftest] session1 > update testtx set money=money+100 where name='B';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 09:07:58 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session1 has seen a change in the amount, but has not yet submitted it.

At this point, go to session2 and session3 for query.

Root@database-one 09:02:45 [gftest] session2 > root@database-one 09:12:23 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:04:10 [gftest] session3 > root@database-one 09:14:12 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session2 and session3 did not see any change in the amount.

A confirm the transfer, that is, submit it.

Root@database-one 09:09:28 [gftest] session1 > commit;Query OK, 0 rows affected (0.00 sec) root@database-one 09:18:03 [gftest] session1 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

At this point, go to session2 and session3 respectively for query.

Root@database-one 09:12:28 [gftest] session2 > root@database-one 09:18:15 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:14:22 [gftest] session3 > root@database-one 09:18:24 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session2 and session3 have not seen any change in the amount. Because they are still in their own transaction (the transaction is implicitly opened by the first select * from testtx of their own session), they really should not see it according to the principle of REPEATABLE READ transaction isolation.

When session2 and session3 finish the current transaction, you can see the change by querying it again.

Root@database-one 09:18:20 [gftest] session2 > root@database-one 09:26:58 [gftest] session2 > commit;Query OK, 0 rows affected (sec) root@database-one 09:27:05 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:18:26 [gftest] session3 > root@database-one 09:27:17 [gftest] session3 > rollback Query OK, 0 rows affected (0.00 sec) root@database-one 09:27:24 [gftest] session3 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

READ COMMITTED, even within the same transaction, each consistent read operation sets up and reads its own new snapshot.

We restore the data and adjust the transaction isolation level for all three sessions to READ COMMITTED.

Root@database-one 09:38:42 [gftest] session1 > update testtx set money=6000 where name='A';Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0root@database-one 09:39:20 [gftest] session1 > update testtx set money=8000 where name='B';Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 09:39:44 [gftest] session1 > commit Query OK, 0 rows affected (0.00 sec) root@database-one 09:39:49 [gftest] session1 > SET SESSION TRANSACTION ISOLATION LEVEL read committed;Query OK, 0 rows affected (0.00 sec) root@database-one 09:40:33 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:41:31 [gftest] session2 > SET SESSION TRANSACTION ISOLATION LEVEL read committed Query OK, 0 rows affected (0.00 sec) root@database-one 09:41:44 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:42:16 [gftest] session3 > SET SESSION TRANSACTION ISOLATION LEVEL read committed Query OK, 0 rows affected (0.01 sec) root@database-one 09:42:24 [gftest] session3 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

A transfer 100 yuan to B. Simulate in session1.

Root@database-one 09:40:42 [gftest] session1 > update testtx set money=money-100 where name='A';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 09:44:10 [gftest] session1 > update testtx set money=money+100 where name='B';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 09:44:20 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session1 has seen a change in the amount, but has not yet submitted it.

At this point, go to session2 and session3 for query.

Root@database-one 09:42:28 [gftest] session3 > root@database-one 09:47:15 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:42:28 [gftest] session3 > root@database-one 09:47:15 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session2 and session3 did not see any change in the amount.

A confirm the transfer, that is, submit it.

Root@database-one 09:50:37 [gftest] session1 > commit;Query OK, 0 rows affected (0.03 sec) root@database-one 09:50:43 [gftest] session1 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

At this point, query from the perspective of session2 and session3 respectively.

Root@database-one 09:48:02 [gftest] session2 > root@database-one 09:52:18 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 09:48:18 [gftest] session3 > root@database-one 09:53:11 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Both session2 and session3 saw the change of the amount. Because although they are still in their own transaction (the transaction is implicitly opened by the first select * from testtx of their own session), they should see it according to the principle of READ COMMITTED transaction isolation.

READ UNCOMMITTED,SELECT statements are executed in a non-locked manner, but earlier versions of the data may be used, and such reads are inconsistent, so they are also known as dirty reads.

We restore the data and adjust the transaction isolation level for all three sessions to READ COMMITTED.

Root@database-one 10:02:49 [gftest] session1 > update testtx set money=6000 where name='A';Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0root@database-one 10:03:10 [gftest] session1 > update testtx set money=8000 where name='B';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 10:03:20 [gftest] session1 > commit Query OK, 0 rows affected (0.00 sec) root@database-one 10:03:30 [gftest] session1 > SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;Query OK, 0 rows affected (0.00 sec) root@database-one 10:03:49 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 10:02:52 [gftest] session2 > SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted Query OK, 0 rows affected (0.00 sec) root@database-one 10:04:58 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 10:05:35 [gftest] session3 > SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted Query OK, 0 rows affected (0.00 sec) root@database-one 10:05:37 [gftest] session3 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

A transfer 100 yuan to B. Simulate in session1.

Root@database-one 10:06:43 [gftest] session1 > update testtx set money=money-100 where name='A';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 10:06:47 [gftest] session1 > update testtx set money=money+100 where name='B';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0root@database-one 10:06:57 [gftest] session1 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session1 has seen a change in the amount, but has not yet submitted it.

At this point, go to session2 and session3 for query.

Root@database-one 10:05:07 [gftest] session2 > root@database-one 10:08:34 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 10:06:02 [gftest] session3 > root@database-one 10:08:42 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Session2 sees the amount change, session3 does not see the amount change. Because although they are still in their own transaction (the transaction is implicitly opened by the first select * from testtx of their own session), according to the principle of READ UNCOMMITTED transaction isolation, session3 does not see the amount change because an earlier version of the data is used. It should be noted here that sometimes session2 may see a change in the amount, sometimes session3 may see a change in the amount, sometimes both session2 and session3 may see a change in the amount, and sometimes both session2 and session3 may not see a change in the amount, which is determined instantly by MySQL based on the version of the data.

A confirm the transfer, that is, submit it.

Root@database-one 10:35:52 [gftest] session1 > commit;Query OK, 0 rows affected (0.01 sec) root@database-one 10:36:01 [gftest] session1 > select * from testtx;+-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (0.00 sec)

At this point, query from the perspective of session2 and session3 respectively.

Root@database-one 10:09:24 [gftest] session2 > root@database-one 11:09:45 [gftest] session2 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 11:08:29 [gftest] session3 > root@database-one 11:11:54 [gftest] session3 > select * from testtx +-+-+ | name | money | +-+-+ | A | 5900.00 | B | 8100.00 | C | 9000.00 | +-+-+ 3 rows in set (9000.00 sec)

Both session2 and session3 saw the change of the amount.

SERIALIZABLE, this level is similar to REPEATABLE READ, but more stringent. In non-autocommit mode, InnoDB implicitly converts all SELECT statements to SELECT. LOCK IN SHARE MODE . In autocommit mode, SELECT runs as a transaction in its own transaction.

Because the effect is similar to REPEATABLE READ, I will not demonstrate it here. Interested students can verify it for themselves. SERIALIZABLE enforces more stringent rules than REPEATABLE READ and is mainly used in special situations such as XA transactions, concurrency and deadlock resolution scenarios.

Transaction Access Mode (transaction access mode)

The access mode of the transaction is easy to understand, which refers to how to use the data in the table in the transaction, which is divided into READ WRITE and READ ONLY, and the default is READ WRITE.

In the testtx table, let's start a READ ONLY transaction, modify the data in it, and see what happens.

Root@database-one 11:56: [gftest] > select @ @ tx_isolation,@@autocommit +-+-+ | @ @ tx_isolation | @ @ autocommit | +-+-+ | REPEATABLE-READ | 1 | +-- + 1 row in set (0.00 sec) root@database-one 11:57: [gftest] > SET SESSION TRANSACTION read only Query OK, 0 rows affected (0.00 sec) root@database-one 11:57: [gftest] > start transaction;Query OK, 0 rows affected (0.00 sec) root@database-one 11:59: [gftest] > select * from testtx +-+-+ | name | money | +-+-+ | A | 6000.00 | B | 8000.00 | C | 9000.00 | +-+-+ 3 rows in set (sec) root@database-one 11:59: [gftest] > update testtx set money=0 where name='A';ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

As you can see, transactions in READ ONLY mode report errors when they modify data.

Transaction Characteristic Scope (scope of transaction attributes)

Careful students may have noticed that there are optional keywords GLOBAL and SESSION in SET TRANSACTION, which determine the scope of transaction attributes.

When using GLOBAL, this statement affects all subsequent sessions, and existing sessions are not affected.

When using SESSION, this statement affects all subsequent transactions in the current session.

When GLOBAL or SESSION is not used, this statement affects only the next transaction executed in the session.

On the MySQL SET TRANSACTION will not affect the transaction to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.