文章目录
在删改数据的时候,如何避免锁表?
对于这个问题,有的童鞋懵逼了,啥是锁表?这是MysQL平时在执行sql的时候会自动加锁,所以很多童鞋不知道锁机制也是很正常的。下面就介绍下MySQL的锁机制
什么是锁机制
因为我们平常使用的是 InnoDB,就说下InnoDB的锁机制,采用的是行级锁,这个比MyISAM引擎在写入的时候会把整个表锁住,其它的连接可以读取但是不能向数据表添加修改删除数据,这就造成了MyISAM引擎的并发写操作非常的差劲。
但是InnoDB的行级锁不会锁住整个表,只会锁住sql语句用到的数据。比如说,现在有一个update语句要执行3条数据的修改,那么MySQL就会把这3条数据给锁住,而不会锁住其它的记录。
你可能想问锁住数据后,会发生什么呢?不能读还是不能写呢?接下来详细说下行级锁的分类和特点
共享锁和排它锁
我们使用的MySQL数据库InnoDB引擎使用的是行级锁,只会锁住记录,我们对没有锁住的记录进行修改和删除,甚至是添加记录都是可以的,所以行级锁的并发性能要好很多,这也是采用INnoDB引擎的一个重要原因,Innodb引擎的行级锁共分为两种:
- 共享锁(S锁)和排它锁(X锁)
这两种锁很好区分,效果差不多,都是不允许其它事务进行写操作,但是读取数据是可以的。写操作指的是insert、update、delete,因为会改变数据文件。
排它锁只要对数据加了这种锁,就不能给数据加任何锁了,锁机制是排它的。共享锁可不是这样的效果,你给数据加了共享锁,其它事务也可以给数据加共享锁,共享锁并不是排它的。
共享锁
我们看看共享锁如何使用,默认情况下MySQL是不会给数据加共享锁的,因为共享锁不经常被使用,所以我们需要手动给数据加共享锁,或者在serializable(顺序读)事务隔离级别下,Mysql会给数据添加共享锁。鉴于serializable这种事务隔离级别不推荐使用,所以我们想要体验共享锁,那就得手动给数据加共享锁了,比如下面语句
select ... from ... lock in share mode;
上面lock in share mode
子句就是对select查询到的数据添加共享锁,如果事务不提交,共享锁就不释放,只有事务提交之后,这个共享锁才会释放。共享锁在释放之前,其它的事务是可以读取数据的,但是不能修改数据。
例子:共享锁在释放之前,其它的事务不能修改数据
我们看个例子:
begin;
select * from t_test where id<=10 lock in share mode;
t_test表有1000万条数据,我们一次对1000万条数据加锁还是耗时很长的,所以我们用where id<10
对10条数据加共享锁
接着,我们在另一个事务里对前100条数据修改
begin;
update t_test set val="ABC" where id<=100;
执行后,看到begin执行完了,但是update还在等待,一直等待第一个事务释放
我们现在对第一个事务释放下,即执行下事务的提交或回滚
commit;
再回到第二个事务,我们执行后可以发现begin执行后,update也会执行。
例子:两个事务对同样的数据加共享锁
共享锁不是排它的,第一个事务对数据加共享锁,第二个事务可以对同样的数据加共享锁。各位童鞋,我们将上个例子的sql语句做下改造
第一个事务:
begin;
select * from t_test where id<=10 lock in share mode;
第二个事务:
begin;
select * from t_test where id<=100 lock in share mode;
第一个sql执行后,发现第二个sql对同样数据加共享锁也成功了
排它锁
平时我们执行insert、delete、update语句时候,mysql会自动给数据加上排它锁,等到事务提交或者回滚的时候会自动释放排它锁。因此说,我们可以不用主动给数据加锁,mysql也会帮我们数据加上排它锁。另外我们如果想手动给数据加上排它锁,也是可以的。
select ... from ... for update;
手动加排它锁的sql语句写成上面的样子,在查询语句后面加上for update
,就自动加上了排它锁。童鞋们需要注意,对加上排它锁的数据是可以读取的,但是不可以写入,并且不可以再加任何的锁机制
例子:没有加锁的子句,但是数据库自动加锁
第一个sql事务:
begin;
update t_test set val="ABC" where id<=100;
看到上面update语句并没有任何加锁的子句,执行时候,mysql会自动给前100条数据加上排它锁,这个事务不提交也不回滚,证明锁是加着没有释放
第二个事务的sql
begin;
delete from t_test where id<=10;
执行后,可以发现
没有任何结果,证明在等待锁的释放,这也就印证了正常的写操作没有加锁,但是数据库在执行sql语句时候,自动加上排它锁
例子:手动加锁
第一个事务的sql
begin;
select * from t_test where id<=100 for update;
第二个事务的sql:
begin;
select * from t_test where id<=10 for update ;
第一个执行后,再执行第二个,发现在等待锁的释放,也就是真的排它锁是排它的,只要加上锁,其它的事务是不能对这个数据加上锁的
如何减少并发操作的锁冲突
上面的例子也看到了,数据只要加了共享锁,其它的事务是不能对数据进行写操作,只能等待锁的释放,如果能减少锁冲突,或者降低锁冲突,对提高并发性都是很有帮助的。
解决锁冲突的办法:
- 把复杂的sql语句拆分成简单的sql语句
mysql执行越复杂的sql语句耗时越长,如果改成多条简单的sql语句来执行,这样执行的速度很快,锁数据的时间变短了,这样其它事务的并发写操作也就有机会执行了。 - 对sql语句的优化,也能减少锁住记录时间的效果
比如说子查询的效率很低,但是改成from子句的子查询执行的效率就会高很多,锁住数据的时间也就变短了