MySQL transaction study notes (1) first encounter

How to start a transaction

In "Introduction to Database Transactions", we have discussed the basic concepts and properties of transactions, so how to treat a series of operations as a transaction in MySQL? Like the following:

BEGIN;  //代表开启事务

UPDATE  Student SET name = '张三三' WHERE id = '1';

COMMIT; // 代表将BEGIN和COMMIT之间的语句进行提交,也就是请求MySQL将结果刷新到磁盘上

If there is only BEGIN and no COMMIT, it means that the operation of the data under BEGIN has not been flushed to the disk, which means that the transaction is in a partially committed state. When the transaction isolation level is not re-read, other sessions cannot read the update. ,pass through:

 SHOW VARIABLES LIKE 'transaction_isolation';

You can view the default isolation level of transactions in MySQL:

You can see that the current isolation level of my database is repeatable read. In this case, other Sessions cannot read the updates of this uncommitted transaction. Let's verify:

Now let's open another session, that is, open another command line interface, and see if we can query the update of the corresponding session in the above figure.

You can see that the name is still Zhang Sansan. Now we execute the Commit command and submit the transaction, which is equivalent to requesting MySQL to flush the changes made by the transaction to the disk. Then see if another Session can be queried.

auto commit

Some students may ask, I usually execute a single UPDATE statement directly, and I can directly query it in other sessions. Is it not a transaction without an UPDATE statement? Otherwise, why did I execute an UPDATE and other sessions? Can you find it right away? Like the following:

If we do not explicitly use BEGIN or START TRANSACTION (more comprehensive than BEGIN) to start a transaction, then each DML statement (INSERT, UPDATE, DELETE) is regarded as an independent transaction and is automatically committed. This feature is called For automatic committing of transactions, in MySQL you can pass:

SHOW VARIABLES LIKE 'autocommit';

Check if this feature is enabled:

implicit commit

If we don't want to use automatic commit, we can use BGEIN and COMMIT statements to commit manually, or turn off autocommit, but even if we use BGEIN, there is no 100% guarantee that MySQL will not commit automatically, in the following cases:

• If BEGIN is followed by a DDL statement (Data definition language that defines or modifies database objects)
• Implicitly use or modify mysql (this is the database that comes with MySQL, called mysql) tables in the database
• Transaction control or statement about locking (BEGIN is followed by a BEGIN or START TRANSACTION)
• Or turn on the autocommit switch in the current Session
• Or using LOCK TABLE, UNLOCK TABLES and other locking statements will implicitly commit the transaction to which the previous statement belongs.
• The statement that loads data, for example, when we use the LOAD DATA statement to bring data into the database in batches, the transaction to which the previous statement belongs will also be implicitly committed
• Some statements replicated by MySQL will also trigger implicit commit when using START SLAVE, STOP SLAVE, RESET SLAVE and other statements
• Statements such as ANALYZE TABLE [table name], CACHE INDEX, and CHECK TABLE [table name] will also trigger the transaction to which the preceding statement belongs.

Abort the transaction manually

Then if there is an accident and I want to roll back or manually abort the transaction, we can use the RollBack command, like the following:

It should be emphasized here that the ROLLBACK statement is a statement provided by MySQL when it needs to manually roll back a transaction. If a MySQL transaction encounters some errors during execution and cannot continue to execute, the transaction will be automatically rolled back .

save point

If we execute a lot of UPDATE statements after Begin, we just want to roll back to a certain point, and then execute it again, please save the point comrade to speak up, put a few points on the statement corresponding to the transaction, and call the ROLLBACK statement , we can roll back to the point where we want to roll back. Examples are as follows:

Syntax example:

SAVEPOINT 保存点名称  // 产生保存点
RELEASE SAVEPOINT 保存点名称 // 删除保存点

Another way to start a transaction

Above we all use the BEGIN statement to start a transaction. There is another syntax for opening a transaction in MySQL:

START TRANSACTION [修饰符],[修饰符],[修饰符];

The difference between START TRANSACTION and BEGIN statement is that START TRANSACATION can be followed by modifiers, as follows:

• READ ONLY (read-only mode)

In this mode, only data is allowed to be read, and data of permanent tables is not allowed to be written. Data of temporary tables can be written, and temporary tables only exist in the current session.

Example:

• WRITE ONLY write-only mode
• WITH CONSISTENT SNAPSHOT Consistent reads.

The mode of a transaction is that it cannot be read-only or write-only. The default is to allow both read and read without modification.