Learn more about transactions and locks in MySQL

MySQL database is a multi-user access system, so when multiple users read and update data at the same time, the data will not be destroyed, so locks are born. A concurrency control technology. When a user tries to modify records in the database, he must first acquire a lock. Then, while the user holding the lock is still modifying, other users cannot modify these records. [Related recommendations: mysql video tutorial]

Locks in MySQL

But compared to other databases, MySQL’s locking mechanism is relatively simple. Different MySQL storage engines have different locking mechanisms. The MylSAM and MEMORY storage engines use table-level locks, the BDB storage engine uses page locks, and the commonly used InnoDB storage engine supports row-level locks and table-level locks. By default Row-level locking is used.

The characteristics of these three types of locks are as follows:

• Table-level locks: low overhead, fast locking, no deadlocks, large locking granularity, and lock conflicts. has the highest probability and the lowest concurrency.

• Row-level lock: high overhead, slow locking, deadlock will occur, the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.

• Page lock: The cost and locking time are between table locks and row locks. Deadlock will occur. The locking granularity is between table locks and row locks. The concurrency is average. .

MyISAM

MyISAM table lock

MySQL provides There are two types of locks, they are:

• READ LOCK: Allows users to only read data from the table.

• WRITE LOCK: Allows users to read and write to the table.

MyISAM's read operation on the table will not block other users' read requests for the same table, but it will block write requests on the same table. MyISAM's write operations on the table will block The read and write operations of other users on the same table, the read operations and write operations of the MyISAM table, and the write operations are serial.

MyISAM will automatically add read locks to all tables used before executing the query statement (SELECT), and will automatically add writes to the tables involved before executing update operations (UPDATE, DELETE, INSERT, etc.) Lock, this process does not require our manual intervention, so we generally do not need to use the LOCK TABLE command to explicitly lock the MyISAM table, but there is no problem in displaying the lock.

Also when you use LOCK TABLES to explicitly add table locks to a table, you must obtain all locks on the tables involved at the same time, because after executing LOCK TABLES, you can only access the explicitly locked tables, not the tables. Unlocked tables, otherwise an error will occur. At the same time, if a read lock is added, only query operations can be performed, and update operations cannot be performed. Otherwise, an error will be reported. The same is true in the case of automatic locking. This is exactly MyISAM tables will not cause deadlocks.

Let’s look at an example.

1. Create a table

CREATE TABLE test_table (   
      Id INT NOT NULL AUTO_INCREMENT,   
      Name VARCHAR(50) NOT NULL,   
      Message VARCHAR(80) NOT NULL,  
      PRIMARY KEY (Id)  
);

2. Session 1 acquires the write lock

mysql> lock table  test_table write;
Query OK, 0 rows affected (0.01 sec)

3. Session 2 reads.

We know that when a session holds a WRITE lock, all other sessions cannot access the data of the table, so when the second session executes the following statement, it will always be in a waiting state.

mysql> select * from test_table;

4. Session 1 unlocking

unlock table;

Concurrent insertion

Read and write operations in MyISAM are serial, but they can be set according to concurrent_insert , let MyISAM support parallel queries and inserts.

concurrent_insert has the following values:

• 0: Concurrent insertion function is not allowed.

• 1: Allow concurrent inserts for tables without holes, with new data at the end of the data file (default).

• 2: Regardless of whether the table has holes or not, concurrent insertion at the end of the data file is allowed.

Holes refer to rows in the middle of the table that have not been deleted.

InnoDB

InnoDB is different from MyISAM. It has two characteristics. One is that it supports transactions, and the other is that it uses row-level locks. There are many differences between level locks and table locks.

Transaction characteristics

• Atomicity

  A transaction is an atomic operation unit. All modifications to the data are either executed, Or do nothing.

• Consistency

  Data must remain consistent at the beginning and completion of a transaction. This means that all relevant data rules must be applied to transaction modifications to maintain data integrity.

• Isolation

  The database system ensures that transactions are not affected by external concurrent operations and can be executed in an "independent" environment, which means that the intermediate state during transaction processing is The outside is not visible.

• Persistence

  After the transaction is completed, its modification to the data is permanent and can be maintained even if a system failure occurs.

Problems caused by concurrent transaction processing

Compared with serial processing, although resource utilization is improved, it can support more users, but concurrent transaction processing will also bring some problems, mainly including the following situations.

Update lost

由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，也就是最后的更新覆盖了由其他事务所做的更新。

脏读

脏读又称无效数据的读出，当事务1将某一值修改后，然后事务2读取该值，后面事务1又因为一些原因撤销对该值的修改，这就导致了事务2所读取到的数据是无效的。

不可重复读

指的是一个事务在读取某些数据后，再次读取之前读过的数据，却发现读出的数据已经发生了改变。

幻读

当事务1按相同的查询条件重新读取以前查询过的数据时，却发现其他事务插入了满足这个条件的新数据。

事务隔离级别

上面说的"更新丢失"是应该完全避免的，但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁。

而脏读、不可重复读、幻读，都是数据库读一致性问题，必须由数据库提供事务隔离机制来解决。数据库实现事务隔离的方式，可分为以下两种，一种是在读取数据前加锁，阻止其他事务对数据进行修改，另一种不需要锁，通过MVCC或MCC来实现，这种技术叫做数据多版本并发控制，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行。

InnoDB有四个事务隔离级别： READ UNCOMMITTED， READ COMMITTED， REPEATABLE READ，和 SERIALIZABLE。默认隔离级别是REPEATABLE READ。

隔离级别	脏读	不可重复性	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读取	×	×	√
可序列化（serializable）	×	×	×

查询/更改隔离级别

显示隔离级别
show global variables like '%isolation%';
select @@transaction_isolation;

设置隔离级别
set global transaction_isolation ='read-committed';
set session transaction isolation level read uncommitted;

READ UNCOMMITTED（读未提交）

在这个隔离级别，所有事务都可以看到其他未提交事务的执行结果。这种隔离级别在实际应用中很少使用，读取未提交的数据也称为脏读。

例子

启动两个会话，并设置隔离级别为READ UNCOMMITTED。

mysql> select * from user;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |     100 |
| 李四      |     100 |
| 王五      |      80 |
+-----------+---------+

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额80
T5	commit	commit

可以看到，在T4时刻，事务1没有提交，但是事务2可以看到被事务1锁更改的数据。

READ COMMITTED （读已提交）

这是大多数数据库系统的默认隔离级别，但不是MySQL的默认级别，他避免了脏读现象，因为在任何未提交的事务前，对任何其他事务都是不可见的，也就是其他事务看不到未提交的数据，允许不可重复读。

例子

将两个会话中隔离级别设置为读已提交
set session transaction isolation level read committed;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三"; 此时张三余额100
T3		select * from user where user_name="张三"; 此时张三余额100
T4	update user set balance =80 where user_name ="张三";
T4		select * from user where user_name="张三"; 此时张三余额100
T5	commit
T5		select * from user where user_name="张三"; 此时张三余额80

可以看到，在T4时刻，事务1没有提交，但是事务2读取到的数据还是100,当事务1提交后，事务2才可以看到。

REPEATABLE READ （可重复读）

这是 MySQL 的默认事务隔离级别，它确保同一事务读取数据时，将看到相同的数据行，但是会出现幻读，当事务1按条件进行查询后，另一个事务在该范围内插入一个新数据，那么事务1再次读取时，就会读到这个新数据。InnoDB 和 Falcon 存储引擎通过 mvcc（多版本并发控制）机制解决了这个问题。

例子

设置两个会话隔离级别为可重复读
set session transaction isolation level repeatable read;

时间	事务1	事务2
T1	begin;	begin;
T2	update user set balance =80 where user_name ="张三";
T3	commit;
T4		select * from user where user_name="张三"; 张三余额为100

可以看到，在T3时刻，事务1已经提交更改，但是在T4时刻的事务2中，还是读取到了原来的数据，但是如果事务2在原来的基础上再减10元，那么最终余额是90还是70呢？,答案是70。.

mysql> update user set balance=balance-10 where user_name="张三";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where user_name="张三";
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |      70 |
+-----------+---------+
1 row in set (0.00 sec)

SERIALIZABLE （序列化）

他是最高的隔离级别，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE，所有事务按照顺序依次执行，因此，脏读、不可重复读、幻读都不会出现。但是，由于事务是串行执行，所以效率会大大下降，

例子

设置隔离级别为序列化
set session transaction isolation level serializable;

时间	事务1	事务2
T1	begin;	begin;
T2	select * from user where user_name="张三";
T3		update user set balance =80 where user_name ="张三";

这一次，有趣的是，事务2在T3时刻更新被阻止了,原因是在serializable隔离级别下，MySQL隐式地将所有普通SELECT查询转换为SELECT FOR SHARE， 持有SELECT FOR SHARE锁的事务只允许其他事务对SELECT行进行处理，而不允许其他事务UPDATE或DELETE它们。

所以有了这个锁定机制，我们之前看到的不一致数据场景就不再可能了。

但是，这个锁具有超时时间，在等待一会后，如果其他事务在这段时间内没有提交或回滚释放锁，将抛出锁等待超时错误，如下所示：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB行锁

InnoDB 的行级锁也分为共享锁和排他锁两种。

• 共享锁允许持有锁的事务读取行。

• 独占锁允许持有锁事务的更新或删除行。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，这两种意向锁都是表锁。

• 意向共享锁 事务想要获得一张表中某几行的共享锁。
• 意向排他锁 事务想要获得一张表中某几行的排他锁。

InnoDB 行锁是通过锁定索引上的索引条目来实现的，因此，InnoDB 只有在通过索引条件检索到数据时才使用行级锁；否则，InnoDB 将使用表锁。

我们可以显示的加锁，但对于update、delete、insert语句，InnoDB会自动给涉及的数据集加排他锁，对于普通的 select 语句，InnoDB 不会加任何锁，下面是显示的加锁方式：

• 共享锁：SELECT  FROM table_name WHERE … LOCK IN SHARE MODE
• 排他锁：SELECT * FROM table_name WHERE … FOR UPDATE

Next-Key锁

当我们使用范围条件而不是相等条件检索数据，并请求其共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于在条件范围内但并不存在的记录，叫做间隙(GAP), InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的Next-Key锁。

举例来说，假如user表中只有101条记录，其user_id的值分别是1.2. ..100. 101，当查找大于100的user_id时，使用下面SQL。

select.* from emp where user_id > 100 for update;

这就是一个范围条件的查询， InnoDB不仅会对user_id为101的记录加锁，也会对user_id大于101的"间隙"加锁，虽然这些记录并不存在。

InnoDB使用Next-Key锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

更多编程相关知识，请访问：编程视频！！

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