MySQL의 트랜잭션 및 잠금에 대해 자세히 알아보기

MySQL 데이터베이스는 다중 사용자 액세스 시스템이므로 여러 사용자가 동시에 데이터를 읽고 업데이트해도 데이터가 파괴되지 않으므로 잠금이 탄생했습니다. 사용자가 데이터베이스의 레코드를 수정하려고 하면 먼저 잠금을 획득해야 합니다. 그런 다음 잠금을 보유한 사용자가 계속 수정하는 동안 다른 사용자는 이러한 레코드를 수정할 수 없습니다. [관련 권장사항: mysql 비디오 튜토리얼]

MySQL의 잠금

그러나 다른 데이터베이스에 비해 MySQL의 잠금 메커니즘은 MylSAM 및 MEMORY 스토리지 엔진을 포함하여 다양한 잠금 메커니즘을 가지고 있습니다. 레벨 잠금이 사용되며 BDB 스토리지 엔진은 페이지 잠금을 사용합니다. 일반적으로 사용되는 InnoDB 스토리지 엔진은 행 수준 잠금과 테이블 수준 잠금을 지원하며 기본적으로 행 수준 잠금이 사용됩니다.

이 세 가지 잠금 유형의 특징은 다음과 같습니다.

• 테이블 수준 잠금: 낮은 오버헤드, 빠른 잠금, 교착 상태 없음, 큰 잠금 세분성, 가장 높은 잠금 충돌 가능성 및 가장 낮은 동시성.

• 행 수준 잠금: 높은 오버헤드, 느린 잠금, 교착 상태, 가장 작은 잠금 세분성, 가장 낮은 잠금 충돌 가능성 및 가장 높은 동시성.

• 페이지 잠금: 비용과 잠금 시간은 테이블 잠금과 행 잠금 사이에 있으며 교착 상태가 발생하며 잠금 세분성은 테이블 잠금과 행 잠금 사이에 있으며 동시성은 평균입니다.

MyISAM

MyISAM 테이블 잠금

MySQL은 테이블에 대해 두 가지 유형의 잠금을 제공합니다.

• READ LOCK: 사용자가 테이블에서 데이터를 읽는 것만 허용합니다.

• WRITE LOCK: 사용자가 테이블을 읽고 쓸 수 있도록 허용합니다.

MyISAM의 테이블 읽기 작업은 동일한 테이블에 대한 다른 사용자의 읽기 요청을 차단하지 않지만 동일한 테이블에 대한 쓰기 요청은 차단합니다. 동일한 테이블의 쓰기 작업, MyISAM 테이블의 읽기 작업 및 쓰기 작업, 쓰기 작업은 직렬입니다.

MyISAM은 쿼리 문(SELECT)을 실행하기 전에 사용된 모든 테이블에 자동으로 읽기 잠금을 추가합니다. 업데이트 작업(UPDATE, DELETE, INSERT 등)을 실행하기 전에 관련된 테이블에 자동으로 쓰기 잠금을 추가합니다. 수동 개입이 필요하지 않으므로 일반적으로 MyISAM 테이블을 명시적으로 잠그기 위해 LOCK TABLE 명령을 사용할 필요가 없지만 잠금을 표시하는 데에는 문제가 없습니다.

또한 LOCK TABLES를 사용하여 테이블에 테이블 잠금을 명시적으로 추가하는 경우 관련된 테이블에 대한 모든 잠금을 동시에 획득해야 합니다. LOCK TABLES를 실행한 후에는 명시적으로 잠긴 테이블에만 액세스할 수 있고 잠금 해제된 테이블에는 액세스할 수 없기 때문입니다. 그렇지 않으면 오류가 발생합니다. 동시에 읽기 잠금이 추가되면 쿼리 작업만 수행할 수 있고 업데이트 작업은 수행할 수 없습니다. 이것이 MyISAM 테이블이 교착 상태에 빠지지 않는 이유입니다.

아래 예를 살펴보세요.

1. 테이블을 생성합니다

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

2. 세션 1이 쓰기 잠금을 획득합니다

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

3.

한 세션이 WRITE 잠금을 유지하면 다른 모든 세션이 테이블의 데이터에 액세스할 수 없으므로 두 번째 세션이 다음 명령문을 실행할 때 항상 대기 상태에 있다는 것을 알고 있습니다.

mysql> select * from test_table;

4. 세션 1 잠금 해제됨

unlock table;

동시 삽입

MyISAM의 읽기 및 쓰기 작업은 직렬이지만 Concurrent_insert 설정에 따라 MyISAM은 병렬 쿼리 및 삽입을 지원할 수 있습니다.

concurrent_insert 값은 다음과 같습니다.

• 0: 동시 삽입 기능이 허용되지 않습니다.

• 1: 데이터 파일 끝에 새 데이터가 있는 구멍이 없는 테이블에 대한 동시 삽입을 허용합니다(기본값).

• 2: 테이블에 홀이 있는지 여부에 관계없이 데이터 파일 끝에 동시 삽입이 허용됩니다.

구멍은 테이블 중앙에 있는 삭제되지 않은 행을 의미합니다.

InnoDB

InnoDB는 MyISAM과 다릅니다. 하나는 트랜잭션을 지원한다는 점과 행 수준 잠금을 사용한다는 점입니다. 자물쇠.

트랜잭션 특성

• 원자성

  트랜잭션은 데이터에 대한 모든 수정 사항이 실행되거나 전혀 실행되지 않는 원자적 작업 단위입니다.

• 일관성

  거래가 시작되고 완료될 때 데이터는 일관성을 유지해야 합니다. 이는 데이터 무결성을 유지하기 위해 모든 관련 데이터 규칙을 트랜잭션 수정에 적용해야 함을 의미합니다.

• Isolation

  데이터베이스 시스템은 트랜잭션이 외부 동시 작업의 영향을 받지 않고 "독립적인" 환경에서 실행될 수 있도록 보장합니다. 즉, 트랜잭션 처리 중 중간 상태가 외부에 보이지 않습니다.

• Persistence

  트랜잭션이 완료된 후 데이터 수정 사항은 영구적이며 시스템 오류가 발생하더라도 유지될 수 있습니다.

동시 트랜잭션 처리로 인한 문제

직렬 처리에 비해 리소스 활용도가 향상되고 더 많은 사용자를 지원할 수 있지만 동시 트랜잭션 처리에도 몇 가지 문제가 발생하며 주로 다음과 같은 상황이 발생합니다.

업데이트가 손실되었습니다

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

脏读

脏读又称无效数据的读出，当事务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锁的目的，一方面是为了防止幻读，另一方面， 是为了满足恢复和复制的需要。

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

위 내용은 MySQL의 트랜잭션 및 잠금에 대해 자세히 알아보기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!