mysql基础(5):全局锁、表锁、行锁

一:MYSQL锁设计的初衷

    mysql锁是为了处理并发问题,作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。


    根据加锁的范围,可以分成全局锁、表锁、行锁。


二:全局锁

    1:什么是全局锁?

        全局锁就是对整个数据库实例加锁,命令是:Flush tables with read lock(FTWRL)。加锁之后整个库处于只读状态,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(建表、修改表结构等)和更新类事务的提交语句。


    2:全局锁的典型使用场景?全库逻辑备份。

    

    3:FTWRL备份需要注意?

        a: 如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆

        b: 如果在从库上备份,那么在备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。


    4:逻辑备份工具mysqldump

        官方自带,mysqldump使用参数-single-transaction,导数据之前就会启动一个事务,来确保拿到一致性视图,由于MVCC的支持,这个过程中数据是可以正常更新的。


    5:有了mysqldump,为啥还需要FTWRL?

        mysqldump只适用于所有的表使用事务引擎的库。如果不是备份只能用FTWRL,这也是DBA要求InnoDB代替MyISAM

    

    6:既然要全库只读,为什么不使用set global readonly=true?仍建议使用FTWRL

        a:在有些系统中,readonly的值会被用来做其他逻辑,不如用来判断一个库是主库还是备库,因此,修改global变量的方式影响更大,不建议使用。

        b:在异常处理机制上与差异。如果执行FTWRL命令之后客户端发生异常断开,那么mysql会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。


三:表级锁

    1:表级锁有两种?一种是表锁,一种是元数据锁(meta data lock MDL)

    

    2:表锁

        a:表锁的语法:lock tables ... read/write

        b:释放锁的两种方式?客户端断开的时候自动释放和unlock tables主动释放。

        c:lock tables 除了限制别的线程读写,也限定了本线程接下来的操作对象,如:

            如果在某个线程A中执行 lock tables t1 read, t2 write;这个语句则其他线程写t1,读写t2的语句都会被阻塞。同时,线程A在执行unlock tables之前,也只能执行读t1, 读写t2操作。

        d:在没出现更细粒度的锁,表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎,一般不是lock tables 命令控制并发。

    

    3:元数据锁(MDL)

        a:什么是MDL?MDL不需要使用,在访问一个表的时候会被自动加上。在mysql5.5加入,当对一个表做增删改查的操作的时候,加MDL读锁,当对表结构变更操作的时候,加MDL写锁。

        b:MDL的作用?保证读写的正确性,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。


    4:MDL的读锁和写锁

        a:读锁之间不互斥,因此你可以有多个线程同时对一个表增删改查。

        b:读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。


    5:给一个表加字段、修改字段、加索引会出现什么问题

        阿群博客


        a:sessionA先启动,t表加MDL读锁,由于sessionB也是MDL读锁,可以正常执行。

        b:sessionC会被blocked,因为sessionA的MDL读锁还没有释放,而sessionC需要MDL写锁,因此只能被阻塞。

        c:如果只有sessionC自己被阻塞还没什么关系,但是之后所有要在表t上新申请MDL读锁请求也会被sessionC阻塞。所有对表的增删改查操作都需要先申请MDL读锁,就都被锁住,等于这个表现在完全不可读写了。

        d:如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新session再请求的话,这个库的线程很快就会饱满。

        e:事务中MDL锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。


    6:如何安全地给小表加字段?

        a:解决长事务,事务不提交,就一直会占着MDL锁。在MySQL的information_schema库的innodb_trx表中,可以查到当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。

        b:如果变更的表是一个热点表,数据量不大,但是请求很频繁,不得不加字段,该怎么做?这时候kill未必管用,因为新的请求马上就来了。比较理想的机制,在alter table语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重复这个过程。

        c:MariaDB已经合并了ALiSQL的这个功能,这两个开源分支目前都支持DDL NOWAIT/WAIT n 这个语法

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...


四:行锁和两阶段协议

    1:什么是行锁?

        MySQL的行锁是在引擎层由各个引擎自己实现的,InnoDB的行锁,通过减少锁冲突来提升业务并发度,行锁就是针对数据表中行记录的锁。比如事务A更新了一行,而这个时候事务B也要更新同一行,则必须等事务A的操作完成后才能进行更新。


    2:两阶段锁协议是什么?

        在InnoDB事务中,行锁是在需要的时候才加上的,并不是不需要了就立刻释放,而是要等到事务结束时才释放。


    3:两阶段协议对我们使用事务用什么帮助?

        如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。如一个电影票在线交易业务,顾客A要在影院B购买电影票,业务需要涉及以下操作:

            a:从顾客A账户余额中扣除电影票价

            b:给影院B的账户余额增加这张电影票价

            c:记录一条交易日志。

        需要update两条记录,并insert一条记录 ,为了保证交易的原子性,三个操作放在事务中。怎么安排这个三个语句在事务中的顺序?

如果同时又另外一个顾客C要在影院B买票,那么两个事务冲突的部分就是语句b,因为它们要更新同一个影院账户的余额,需要修改同一行数据。根据两阶段协议,不论怎么安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才释放的。所c、a、b的顺序,那么影院账户余额这一行的锁时间就最少,这就最大程度地减少了事务之间的锁等待,提升了并发度。

        如果这个影院做活动,低价预售一年内所有的电影票,而且这个活动只做一天,于是在活动时间开始的时候,MySQL就挂了,登上服务器一看cpu消耗接近100%,但整个数据库每秒就执行不到100个事务。这是什么原因?死锁和死锁检测


五:死锁和死锁检测

    1:什么是死锁?

        当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。


    2:死锁的例子

        阿群博客

            事务A在等待事务B释放id=2的行锁,而事务B在等待事务A释放id=1的行锁。事务A和事务B在相互等待对方的资源释放,就是进入了死锁状态。


    3:怎么解决死锁?

        a:直接进入等待,直到超时,超时时间通过:innodb_lock_wait_timeout来设置。

        b:发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行,将参数innodb_deallock_detect设置为on,表示开启这个逻辑。

        

        两种方法的利弊:

            a:在InnoDB中,innodb_lock_wait_timeout默认值是50s,当出现死锁以后,第一个被锁住的线程要过50s才会超时退出,然后其他线程才有可能继续执行,对于在线服务来说,这个等待时间无法接受。如果设置1s,出现死锁确实很快就可以借款,如果不是死锁,简单的锁等待,会出现很多误伤。

            b:主动死锁检查,而且innodb_deallock_detect默认值是on,主动死锁检测在发生死锁的时候,是能够快速发现并进行处理的,但是它也有额外负担的:每当一个事务被锁的时候,就要看看所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待。


    4:上面说到所有事务都要更新同一行的场景呢?

        每个新来的被堵住的线程,都要判断会不会由于自己的加入导致了死锁,这是一个时间复杂度是O(n)的操作。假设1000个并发线程要同时更新同一行,那么死锁检测操作就是100万这个量级的。虽然最终检测的结果没有死锁,但是这期间要消耗大量的cpu资源。因此你就会看到cpu利用率很高,但是每秒执行不了几个事务。


    5:怎么解决热点行更新导致的性能问题呢?

        问题的症结在于,死锁检测要耗费大量的cpu资源。

        a:头痛医头的方法:

            如果能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。这种操作本身有一定的风险,因为业务设计的时候一般不会把死锁当作一个严重的错误,毕竟出现死锁了,就回滚,然后通过业务重试一般就没问题了,这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时,这是业务有损的。


        b:另一种思路控制并发度:

            如果并发能够控制住,比如同一行同时最多只有10个线程在更新,那么死锁检测的成本很低就不会出现这个问题。

            一是:在客户端做并发控制,这个方法不太可行,因为客户端很多,一个应用,有600个客户端,即使每个客户端控制到只有5个并发线程,汇总到数据库服务端以后,峰值并发数也可能达到3000。

            二是:在数据库服务端:数据库中间件或修改MySQL源码,基本思路就是,对于相同行的更新,在进入引擎之前排队,这样在InnoDB内部就不会有大量的死锁检测工作了。

            三是:设计优化上(没有数据库方面的专家实现不了二的方案):通过将一行改成逻辑上的多行来减少锁冲突。以影院账户为例,可以考虑放在多条记录上,比如10个记录,影院账户余额等于这个10个记录的值的总和,这样每次要给影院账户加金额的时候,随机选其中一条记录来加,这样每次冲突概率变成原来的1/10,可以减少锁等待个数,也就减少死锁检测的CPU消耗。


            这个方案看上去是无损的,但其实这类方案需要根据业务逻辑做详细记录,如果账户余额可能会减少,比如退票逻辑,那么这个时候需要考虑当一部分记录变成0的时候,代码要有特殊处理。


六:总结

    1:根据两阶段协议,开发时安排正确的事务语句:

        如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。


    2:调整语句并不能完全避免死锁,所以引入死锁和死锁检测

        三个方案减少死锁对数据库的影响:1是在客户端做并发控制,2是在数据库服务端,3是在设计优化上,总的来说就是减少死锁的主要方向,控制访问相同资源的并发事务量。


阿群博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论