一、数据库引擎
mysql常用的数据库引擎也就是myIsam和innoDB两种,相比较起myIsam而言innoDB支持了事务、外键等功能,具有更好的并发性支持,所以在大并发的情况的下我们一般选择的是innoDB来作为我们的数据库存储引擎,而myIsam相较innoDB的执行性能上会有更好的表现,(毕竟myIsam是mysql自带的引擎,而innoDB是由别人写的)。mysql锁级别包括三种:表锁(对整个表加锁)、页锁(介于表锁和行锁之间)、行锁(对具体行加锁),对于myIsam默认采用的是表锁,而对于innoDB默认采用的是行锁,
innoDB的行锁是通过共享锁和排他锁实现的,具体参考mysql锁机制之共享锁和排它锁,
共享锁:select * from table_name where .....lock in share mode
排他锁:select * from table_name where .....for update
由于mysql事务默认是自动提交的,如果要测试锁机制,记得把事务自动提交关闭
set autoCommit=0
二、数据库事务
事务具有ACID的特性,也就是原子性、持久性、隔离性和一致性,mysql中innoDB支持事务,而myIsam不支持,采用事务对于业务逻辑和并发都有很大的帮助,但是采用事务也会导致相应的问题,
1、丢失更新:两个事务同时对同一行进行修改,会有一个事务的更新被覆盖。(写写)
2、脏读:一个事务对一行记录进行读取,另一个事务同时对这行记录进行修改,如果写事务中途失败回滚,而读事务却读取到写事务修改的值,这样就造成了脏读。(读写)
3、不可重复读:一个事务对一行记录进行两次读取,另一个事务同时对这一行进行修改,这个会导致第一次读取的记录和第二次读取的记录不一致,出现了不可重复读。(读写读)
4、幻读:一个事务对表进行两次读取,另一个事务同时进行插入操作,这回导致第二次读取出现 了第一次没有出现的记录,也就是幻读。
解决办法也就是设置事务的隔离级别:读未提交(read-uncommitted)、读提交(read-committed)、可重复读(read-reapted)、序列化(Serializable)。
事务的隔离级别越高,那么出现事务也就越安全,但是加锁机制也就越复杂,性能消耗越大。
mysql默认采用的是可重复读的级别
select @@tx_isolation 可以查看
三、innoDB的行锁
innoDB默认采用的是行锁,但是采用行锁必须要查询条件为索引列,如果查询条件不是为索引列,那么mysql会采用表锁机制。接下来就来做一下验证。
1、建一个简单的表:
create table test(
a int null,
b int null,
index a_index (a) using BTREE
)engine=innodb;
这个表有两列a和b,都是int类型,其中a是建了索引,
插入四行:
insert into test(a,b) values(1,2);
insert into test(a,b) values(3,4);
insert into test(a,b) values(3,6);
insert into test(a,b) values(8,2);
2、 开启两个session:
session1 | sessio2 |
set autocommit=0 | set autocommit=0 |
select * from test where b=4 for update; 正确查询出结果 | |
select * from test where b =6 for update; 查询被锁死,处于等待状态 | |
commit | |
表锁被释放,查询出结果,commit | |
select * from test where a =1 for update; 正确查询出结果 | |
select * from test where a=8 for update; 正确查询出结果,a是索引列,where条件使用索引列,默认采用行锁机制。 | |
commit | |
commit |
...... for update 是加排它锁。接下来验证事务是否采用行锁。
session1 | session2 |
set autocommit=0 | set autocommit=0 |
start transaction | start transaction |
update test set a=10 where b=4 for update; 正确更新 | |
update test set a=20 where b=6 for update; 更新等待,出现表锁, | |
commit | |
锁被释放,正确更新,commit | |
update test set b=10 where a=10 for update; 正确更新 | |
update test set b=20 where a=20 for update; 正确更新,a是索引列,where条件使用索引列,默认采用行锁机制,不同行不会被锁死 | |
commit | |
commit |
innoDB的行锁在查询条件为索引列的情况下才采用,所以查询条件尽量采用索引列。
间隙锁
innoDB中面对between...and....这种区间查询的时候采用的是间隙锁机制,
update test set a=.... where b between x and y 那么从[x,y]这个区间都会加上行锁。
四、总结
1、在开发过程中尽量降低事务的粗粒度,避免过多的查询和更新和插入操作堆在一起,从而造成长时间的锁等待。
影响程序运行效率。
2、查询条件尽量采用索引列,从而使mysql采用行锁,而不是表锁。
3、使用区间查询的时候注意区间范围,避免锁住其他不必要锁住的行。
4、合理的采用事务的隔离级别,事务隔离级别越高,性能越低,锁机制越复杂,发生的死锁的概率越大,
有时候降低事务的隔离级别也是解决死锁的有效方式。