表锁:共享读锁 和 独占写锁
表锁的优势:开销小;加锁快;无死锁
表锁的劣势:锁粒度大,发生锁冲突的概率高,并发处理能力低
加锁的方式:自动加锁。查询操作(SELECT),会自动给涉及的所有表加读锁,更新操作(UPDATE、DELETE、INSERT),会自动给涉及的表加写锁。也可以显示加锁:
共享读锁:lock table tableName read;
独占写锁:lock table tableName write;
批量解锁:unlock tables;
共享读锁
对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读操作,但会阻塞对同一表的写操作。只有当读锁释放后,才能执行其他进程的写操作。在锁释放前不能读取,操作其他表。
示例:
LOCK table student READ # 加共享读锁
select * from student # 查询后有结果
select * from emp #查询其他表,报Table 'emp' was not locked with LOCK TABLES 即该表没有共享读锁,不能操作
update student set s_name='zhao' where s_id='01' # Table 'student' was locked with a READ lock and can't be updated 即 只读不能操作
unlock tables; #批量解锁
另外再开一个进程
select * from student # 另开以进程,发现可读
select * from emp # 另开以进程,发现可读
update student set s_name='赵' where s_id='01' # 另开以进程,发现进入锁等待,可能发生死锁
当我们释放所有的锁时,批量解锁:unlock tables;
unlock tables;
会发现,刚才在等待的 update 语句执行完毕。
再次证明:表加共享读锁后,不会阻塞其他进程对同一表的读操作,但会阻塞对同一表的写操作。只有当读锁释放后,才能执行其他进程的写操作。在锁释放前不能读取,操作其他表。
独占写锁
对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。在锁释放前不能写其他表。
LOCK TABLE student WRITE; #加表写锁
UPDATE student SET sname='zhao' where s_id='01'; #正常执行
SELECT * from student;#正常执行
select * from emp;#Table 'innodb_lock' was not locked with LOCK TABLES
update emp set v='1001' where k='1';#Table 'innodb_lock' was not locked with LOCK TABLES
UNLOCK TABLES #释放锁
另外开一进程
select * from student;#进入等待
当我们释放所有的锁时,批量解锁:unlock tables;
unlock tables;
查询语句执行
总结证明:表加独占写锁,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作。在锁释放前不能写其他表。
什么场景下用表锁
InnoDB默认采用行锁,在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。
即便你在条件中使用了索引字段,MySQL会根据自身的执行计划,考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
第一种情况:全表更新。事务需要更新大部分或全部数据,且表又比较大。若使用行锁,会导致事务执行效率低,从而可能造成其他事务长时间锁等待和更多的锁冲突。
第二种情况:多表查询。事务涉及多个表,比较复杂的关联查询,很可能引起死锁,造成大量事务回滚。这种情况若能一次性锁定事务涉及的表,从而可以避免死锁、减少数据库因事务回滚带来的开销。