Detailed explanation of transactions in MySQL

MySQL transaction:

1.MySQL transaction control statement

(1).Open transaction

begin;

(2).Submit transaction

commit;

(3).Rollback the transaction

rollback;

(4).Set whether the transaction is automatically committed

set autocommit = {0 | 1}; // 0不自动提交,1自动提交

For example:

begin;// 开启事务
insert into table_name values(内容);
commit;// 提交事务

This is a simple transaction that inserts data

begin;// 开启事务
insert into table_name values(内容);// 后悔了,不想插入数据了
rollback;// 回滚,取消这次的事务操作

This is a simple rollback transaction that inserts data

2.MySQL transaction isolation statement

View the isolation level of MySQL

show variables like 'tx_isolation';

(1). read-uncommitted (read uncommitted content)

Dirty reading (problems caused);

The content of a user's two query results is different.

Set the default isolation level (you need to log in again after the setting is completed):

set global transaction isolation level read uncommitted;

Two users log in to the database at the same time and start transactions respectively. One user's transaction performs addition, deletion and modification operations, and the other user's transaction only performs query operations. The former performs the operation without committing, and the latter can see the operation results. However, if the former rolls back the transaction at this time, the latter's query will see different results from the last time (also That is, the result of no operation), this is the dirty read problem.

(2).read committed (read the submitted content)

Transactions can only see the changes made by the committed transaction. The same query may return different results, which causes non-repeatable read problems.

Set the default isolation level (you need to log in again after the setting is completed):

set global transaction isolation level read committed;

In a transaction, I saw this Two different query results (similar to dirty read).

(3).repeatable read(repeatable read)

can ensure that multiple instances of the same transaction read data concurrently , you will see the same data row (Mornar isolation level of the database).

Set the default isolation level (you need to log in again after the setting is completed):

set global transaction isolation level repeatable read;

That’s it once During a transaction, no matter how another user modifies the data, the results queried in this transaction are always the same.

***Implicit transaction commit***:

alter table users engine = innodb;// 会造成事务的隐式提交

3. InnoDB lock mechanism

(1). There are no primary keys and indexes in a table. A transaction will lock all records in the table, and other transactions cannot operate the table.

(2). If there is a primary key or index in a table, a transaction will lock a certain row of data in the table that it operates, and will not lock the entire table. If other transactions operate on other data in the table, this time Not affected. It should be noted that the row-level lock here is a gap lock. That is to say, if you modify certain rows of data, such as modifying n rows of data with id<10.=, then, id = 11 The data will also be locked, which needs to be noted.

The above is the detailed content of Detailed explanation of transactions in MySQL. For more information, please follow other related articles on the PHP Chinese website!