MySQL重点内容-锁
概述
锁是计算机协调多个进程或并发线程访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
分类
MySQL中的锁,按照锁的粒度分,分为以下三类:
1、全局锁:锁定数据库中的所有表
2、表级锁:每次操作锁住整张表
3、行级锁:每次操作锁住对应的行数据
全局锁
全局锁就是对整个数据库实例加锁,当前数据库服务器当中所有的数据库、所有的表
都加上了锁。枷锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句
都将被阻塞。
其典型的使用场景是做全库的逻辑备份
,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
全库的逻辑备份:
1、如下图,先备份tb_stock表
2、执行了扣减库存,生成订单的业务逻辑。
3、备份tb_order表
4、执行插入订单日志的业务逻辑
5、备份tb_orderlog表
最终备份的tb_stock与其他两张表的数据不一致。
加锁之后的的情况:
1、加全局锁(flush tables with read lock),然后执行备份(mysqldump -uroot -p1234 itcast > itcast.sql)
2、这时DML和DDL都会被阻塞,DQL可以执行
3、当数据库完整备份完毕后释放全局锁(unlock tables)
注意:mysqldump -h ip -uroot -p1234 itcast > itcast.sql不是sql命令,而是myslq提供的一个工具,因此在命令行中执行,而不能在交互式sql中运行。
全局锁的特点
数据库中加全局锁,是一个比较重的操作,存在以下问题:
- 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
- 如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致
主从延迟
。
在InnoDB引擎中,我们可以在备份时加上参数–single-transaction(通过快照读)参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -h ip -uroot -p123456 itcast > itcast.sql
表级锁
表级锁,每次操作锁住整张表。锁定力度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
表级锁,主要分为以下三类:
- 表锁
- 元数据锁(meta data lock,MDL)
- 意向锁
1、表锁
表锁又可以分为两类:
1、表共享读锁(read lock),又称读锁
2、表独占写锁(write lock),又称写锁
语法:
- 加锁:lock tables 表名… read/write
- 释放锁:unlock tables / 客户端断开连接
读锁
客户端1在某张表上加了读锁之后,客户端1是可以查询这张表,但是不能修改这张表。客户端2也可以查询该表,而不能修改该表。
写锁
客户端对某张表加了写锁,那么它既能够读也能写该表,但是其他客户端的对该表的读和写会被阻塞。
2、元数据锁(meta data lock,MDL)
MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁的主要作用是维护表的元数据的一致性,在表上有活动的事务的时候,不可以对元数据进行写入操作
。为了避免DML与DDL冲突,保证读写的正确性。
元数据:指的是表结构
在一个事务中,执行select会自动为该表加上SHARED_READ锁,执行增删改会为该表自动加上SHARED_WRITE锁,这两种锁是可以同时存在的,而EXCLUSIVE锁与这两种锁是互斥的。因此,当执行了增删改查的事务没有提交的时候,对该表的修改(alter)操作均会被阻塞,直到事务提交释放了SHARED_READ锁和SHARED_WRITE锁。
3、意向锁
为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB中加入了意向锁,使得表锁不用检查每行数据是否枷锁,使用意向锁来减少表锁的检查。
没有意向锁
的情况:
线程A开启事务,执行update语句,会对update的这一行加上行锁。此时线程B想要对该表加上表锁(读锁或者写锁),就要逐行的检查是否存在行锁,若存在行锁,则线程B被阻塞。这样逐行查询锁的效率极低
。
有意向锁
的情况:
线程A开启事务,执行update语句,先对update的这一行加上行锁,然后为该表加上意向锁。此时线程B想要为该表加上表锁,就要检查是否存在意向锁,意向锁的类型
是否与表锁兼容,若兼容则可以加表锁,否则线程B被阻塞。
意向锁:
-
意向共享锁(IS):由语句select…lock in share mode添加
-
意向排他锁(IX):由insert、update、delete、select…for update添加
-
意向共享锁(IS):与表锁共享锁(read)兼容,与表锁排他锁(write)互斥
-
意向排它锁(IX):与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。