Mysql数据库一般会并发的执行多个事务,同时对一批数据读或写,就可能导致通常说的脏读、脏写、不可重复读、幻读等问题。为了解决这些问题,mysql提供了事务隔离,锁,MVCC等一套机制解决事务并发问题。
事务及其ACID特性:
事务是由一组sql组成的执行单元,事务具有以下特性,简称为ACID:
原子性(Atomicity):事务是一个原子操作单元,要么都执行成功,要么都不成功。
一致性(Consistent): 在事务开始和结束,数据必须保证一致性
隔离性(Isolation):事务的执行不受外部事务的影响,意味着事务的中间状态对外部不可见。
持久性(Durable): 事务完成后,对数据的修改是持久性的,不会因为系统的故障导致数据丢失。
并发事务的问题:
更新丢失或脏写,当两个或多个事务操作同一条数据时,由于每个事务都不知道其它事务的存在,最后一条更新会覆盖其它事务的更新。
脏读,一条事务正在对一条数据处理,在事务未提交之前,这条数据的值是不确定的,如果不加以控制,此时其它事务读到这条数据,并依据这条数据做了进一步处理,就会产生未提交的数据依赖关系。事务B读取到了事务A未提交的数据并做了相应处理,如果事务A进行了回滚,事务B读到的数据是无效的,就破坏了数据的一致性。
不可重复读,事务在读某条数据后的时间点再去读这条数据,发现读到的数据不一致或者数据被删除了,简单来讲,就是一个事务不太时间读到的数据不一样,违反了事务的隔离性。
幻读:一个事务按相同的条件重新读取之前检索过的数据,却发现了其它事务插入的满足该条件的新数据,事务A读到了事务B提交的新数据,不满足事务的隔离性
事务的隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交 | 可能 | 可能 | 可能 |
读未提交 | 不可能 | 可能 | 可能 |
可重复读 | 不可能 | 不可能 | 可能 |
串行化 | 不可能 | 不可能 | 不可能 |
事务的隔离级别越高,发生并发事务问题的可能性也就越小,但是付出的代价也就越大。
锁是计算机协调多个进程或线程并发访问同一个资源的机制。在数据库中,除了cpu,I/O等资源外,数据也是提供给用户供需的一种资源,如果保证数据的一致性和有效性是数据库必须解决的问题,锁冲突是影响并发访问性能的一个重要因素。
锁的分类:按性能可以分为乐观锁和悲观锁,按数据库操作类型可以分为读锁读锁(共享锁,多个读操作可以同时进行)和写锁(排它锁:在写操作没有完成之前会阻断其它读锁和写锁),按操作粒度可以分为表锁和行锁
表锁:每次操作锁定整张表,锁的粒度大,开销小,加锁速度快,不会发生死锁,容易发生锁冲突,并发度小,一般用在整表迁移的时候。
手动增加表锁:lock table 表名 read/write;
查看表加锁情况:show open tables;
删除表锁:unlock tables;
行锁:每次操作锁住一行数据,锁的粒度小,开销大,加锁慢,可能发生死锁,不容易发生锁冲突,并发度最高。(InnoDB支持行级锁,MYISAM只支持表锁),一个session开启事务更新一条记录不提交,其它事务更新同一条数据会阻塞。
MYISAM在执行select时会给操作表加读锁,执行insert,update,delete时加写锁,INNODB在select(非串行化隔离级别)时不加锁,但update,insert,delete时会加行锁。
间隙锁:假设存在id (1,3,8,19) ,那么间隙就有id为(1,3)(3,8)(8,19)(19,正无穷),当执行update .. where id >2 and id <18 时,范围(1,19]的记录包括间隙内为插入的记录都会被加上锁,间隙锁是在事务可重复读情况下才生效。
临键锁:行锁和间隙锁的组合,例如上面的(1,19]的整个区间就叫做临键锁。
无索引行锁会升级成表锁,行锁是加在索引上面的,如果对非索引字段更新,行锁可能会升级为表锁,INNODB的行锁是对索引加的锁,不是对记录加锁,如果该索引实现或未加索引,行锁会升级为表锁
行锁分析:
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数
锁优化建议 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁 合理设计索引,尽量缩小锁的范围 尽可能减少检索条件范围,避免间隙锁 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行 尽可能低级别事务隔离