InnoDB事务和锁

我不是女神ヾ 2022-05-28 23:50 172阅读 0赞

InnoDB事务和锁  
InnoDB支持事务,MyISAM不支持事务.

原文地址: [这里写链接内容][Link 1]  
一.事务的基本特性

ACID特性

1.原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

2.一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

1.  隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

4.持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

二.并发事务带来的问题

1.更新丢失

假设table中的price在更新前为0

如A用户开始一个事务:

BEGIN;

SELECT price FROM table WHERE id=1;

# 开始更新 #

UPDATE table SET price = price + 1 WHERE id=1;

COMMIT;

B用户在A用户未提交事务时,同样更新:

BEGIN;

SELECT price FROM table WHERE id=1; \#此处的price应该是1,但是A用户未提交事务,所以还是0

# 开始更新 #

UPDATE table SET price = price+2 WHERE id=1;

COMMIT;

最终price=2,但实际上应该是3,这就是更新丢失

2.脏读

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”.上面B用户在读取price时,A用户未提交事务,B用户读到就是脏数据.

3.不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。

4.幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

三.事务的隔离级别

在上面讲到的并发事务处理带来的问题中，“更新丢失”通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种。

1.一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

2.另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

3.mysql的四种隔离级别

Ps:

a.Repeatable read是默认隔离级别.

b. 查看当前会话隔离级别:select @@tx\_isolation;

c.查看系统隔离级别: select @@global.tx\_isolation;

四.InnoDB锁争用:

mysql> show global status like ‘innodb\_row\_lock%’;

Innodb\_row\_lock\_current\_waits 0

Innodb\_row\_lock\_time 530

Innodb\_row\_lock\_time\_avg 106

Innodb\_row\_lock\_time\_max 327

Innodb\_row\_lock\_waits 5

如果发现锁争用比较严重，如InnoDB\_row\_lock\_waits和InnoDB\_row\_lock\_time\_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

Ps:InnoDB锁超时时间由变量innodb\_lock\_wait\_timeout控制,默认是50s

五.InnoDB的行锁模式及加锁方法

(一).行锁模式

1.共享锁(S锁):

对同一行数据都可以共享一把锁,但是没有获得锁的事务只可以读,不可以修改

2.排它锁(X锁)

对同一行数据,获得该锁的事务可读可写,未获得锁的事务不可读也不可写.

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

3.意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

4.意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

(二).加锁方法:

Ps:

select语句默认不会加任何锁类型  
update,delete,insert都会自动给涉及到的数据加上排他锁

1.  共享锁

SELECT … LOCK IN SHARE MODE

主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作. 但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

对于加了共享锁的数据行,其他事务可以加共享锁或不加锁,但无法加排它锁.

1.  排它锁

SELECT … FOR UPDATE

六.InnoDB的行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的， InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

1.在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁

在name列上加锁后,上述情况将不存在

1.  MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁等待的
2.  当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

4.即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

如下示例(在mysql5.1版本上,5.5以上版本不存在下列问题):

name列有索引,name字段类型为varchar.

EXPLAIN SELECT \* FROM `test` WHERE `name` =1;\#执行全表扫描

EXPLAIN SELECT \* FROM `test` WHERE `name`=’1’;\#执行索引扫描

七.间隙锁

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

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select \* from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

PS:

在多列条件查找时,

如SELECT \* FROM table WHERE col1=2 AND col2>1000 FOR UPDATE;在col1和col2上均有索引

这个时候

INSERT INTO table(col1,col2) VALUES(3,8);可以执行

INSERT INTO table(col1,col2) VALUES(2,8);不可以执行

InnoDB根据索引只是锁定了需要锁定的间隙锁.

尽量少用不确定的SQL语句如

insert into target\_tab select \* from source\_tab where …

create table new\_tab …select … From source\_tab where …(CTAS)

通过使用“select \* from source\_tab … Into outfile”和“load data infile …”语句组合来间接实现，采用这种方式MySQL不会给source\_tab加锁

七.InnoDB的表锁

注意点:

1.LOCK TABLES tb\_name WRITE;//当前会话对表tb\_name可读可写,其余会话对表tb\_name不可读不可写  
2.LOCK TABLES tb\_name READ;//当前会话对表tb\_name可读不可写,其余会话对表tb\_name可读不可写  
3.你需要一次锁定更新的表  
LOCK TABLES tb1\_name WRITE;  
在锁定过程中,你可以读tbl2\_name,当你需要更新tbl2\_name,你将得到一个表无法锁定的错误  
4.innodb的表锁,开始事务时会自动释放表锁,所以begin;或set autocommit=0;等命令应该在lock tables的前面.

在InnoDB下，使用表锁要注意以下两点。

1.使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb\_table\_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。

2.在用LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：

例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, …;

\[do something with tables t1 and t2 here\];

COMMIT;

UNLOCK TABLES;

八.在应用中避免死锁的方法

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb\_lock\_wait\_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

1.  在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
2.  在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
3.  在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
4.  前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题
5.  当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT…FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重复出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重复异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁

[Link 1]: https://www.cnblogs.com/itfenqing/p/6802497.html