• 锁机制


    MYISAM在查询时会默认加上读锁,插入时默认加上写锁

    读锁 加上后 其他线程可读不可写

    写锁加上后 别的线程不能读不能写,要等持锁的session释放锁

    表锁一般用在查询为主,只有少量按索引条件更新数据的应用,行锁更适合有大量按索引并发更新少量因为行锁很耗资源,大量并发更新,会使得资源开销很大)不同数据,又有并发查询的应用

    MySQL中的表锁兼容性

    请求锁模式 是否兼容当前锁模式 None 读锁 写锁
    读锁
    写锁

    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中,显式加锁基本上都是为了方便而已,并非必须如此。

    给MyISAM表显示加锁,一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如,有一个订单表orders,其中记录有各订单的总金额total,同时还有一个订单明细表order_detail,其中记录有各订单每一产品的金额小计 subtotal,假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL:

    1. 1. Select sum(total) from orders;
      2. Select sum(subtotal) from order_detail;
      3. 这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是:
      4. Lock tables orders read local, order_detail read local;
      5. Select sum(total) from orders;
      6. Select sum(subtotal) from order_detail;
      7. Unlock tables;

    要特别说明以下两点内容。

    • 上面的例子在LOCK TABLES时加了“local”选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录,有关MyISAM表的并发插入问题,在后面的章节中还会进一步介绍。
    • 在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。

    上文提到过MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

    MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。

    • 当concurrent_insert设置为0时,不允许并发插入。
    • 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
    • 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

    在如下表所示的例子中,session_1获得了一个表的READ LOCAL锁,该线程可以对表进行查询操作,但不能对表进行更新操作;其他的线程(session_2),虽然不能对表进行删除和更新操作,但却可以对该表进行并发插入操作,这里假设该表中间不存在空洞。

    MyISAM也支持并发插入

    ​ MyISAM存储引擎的读写(INSERT)并发例子

    可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。

    MyISAM的锁调度

    前面讲过,MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!****这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节MyISAM 的调度行为。

    • 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
    • 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
    • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

    虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。

    另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

    上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

    InnoDB查看行锁的竞争状态

    可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

    1. mysql> show status like 'innodb_row_lock%';
    2. +-------------------------------+-------+
    3. | Variable_name         | Value |
    4. +-------------------------------+-------+
    5. | InnoDB_row_lock_current_waits | 0   |
    6. | InnoDB_row_lock_time     | 0   |
    7. | InnoDB_row_lock_time_avg   | 0   |
    8. | InnoDB_row_lock_time_max   | 0   |
    9. | InnoDB_row_lock_waits     | 0   |
    10. +-------------------------------+-------+
    11. 5 rows in set (0.01 sec)
    
    如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。
    
    1. 具体方法如下:
    2. mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
    3. Query OK, 0 rows affected (0.14 sec)
    4. 然后就可以用下面的语句来进行查看:
    5. mysql> Show innodb statusG;
    6. *************************** 1. row ***************************
    7.  Type: InnoDB
    8.  Name:
    9. Status:
    10. …
    11. …
    12. ------------
    13. TRANSACTIONS
    14. ------------
    15. Trx id counter 0 117472192
    16. Purge done for trx's n:o < 0 117472190 undo n:o < 0 0
    17. History list length 17
    18. Total number of lock structs in row lock hash table 0
    19. LIST OF TRANSACTIONS FOR EACH SESSION:
    20. ---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456
    21. MySQL thread id 200610, query id 291197 localhost root
    22. ---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936
    23. MySQL thread id 199285, query id 291199 localhost root
    24. Show innodb status
    25. …
    26. 监视器可以通过发出下列语句来停止查看:
    27. mysql> DROP TABLE innodb_monitor;
    28. Query OK, 0 rows affected (0.05 sec)

    设置监视器后,在SHOW INNODB STATUS的显示内容中,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等,便于进行进一步的分析和问题的确定。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“--console”选项来启动服务器以关闭写日志文件。

    InnoDB行锁实现方式

    InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

    在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。

    (1)在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。

    在如下所示的例子中,开始tab_no_index表没有索引:

    (3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。

    在如下表所示的例子中,表tab_with_index的id字段有主键索引,name字段有普通索引:

    (4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

    在下面的例子中,检索值的数据类型与索引字段不同,虽然MySQL能够进行数据类型转换,但却不会使用索引,从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划,我们可以清楚地看到了这一点。

    例子中tab_with_index表的name字段有索引,但是name字段是varchar类型的,如果where条件中不是和varchar类型进行比较,则会对name进行类型转换,而执行的全表扫描。

    间隙锁(Next-Key锁)

    当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

    举例来说,假如emp表中只有101条记录,其empid的值分别是 1,2,...,100,101,下面的SQL:

    Select * from emp where empid > 100 for update;

    是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

    InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其它事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另外一方面,是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况,在后续的章节中会做进一步介绍。

    很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

    还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!

    在如下表所示的例子中,假如emp表中只有101条记录,其empid的值分别是1,2,......,100,101。

    ​ InnoDB存储引擎的间隙锁阻塞例子

    session_1 session_2
    mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec) mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)
    当前session对不存在的记录加for update的锁:mysql> select * from emp where empid = 102 for update;Empty set (0.00 sec)
    这时,如果其他session插入empid为102的记录(注意:这条记录并不存在),也会出现锁等待:mysql>insert into emp(empid,...) values(102,...);阻塞等待
    Session_1 执行rollback:mysql> rollback;Query OK, 0 rows affected (13.04 sec)
    由于其他session_1回退后释放了Next-Key锁,当前session可以获得锁并成功插入记录:mysql>insert into emp(empid,...) values(102,...);Query OK, 1 row affected (13.35 sec)

    explain select * from prod





  • 相关阅读:
    【JavaP6大纲】Java基础篇:为什么jdk8以后HashMap会使用红黑树优化?
    【JavaP6大纲】Java基础篇:HashMap加载因子为什么是0.75?
    【JavaP6大纲】Zookeeper篇:选举机制
    就是要幸福(1)严于律人
    天真的童年
    闲言碎语话心得垃圾工作
    镜花水月
    就是要幸福(3)言行自由
    五年
    爸爸我给你捂捂手
  • 原文地址:https://www.cnblogs.com/hualou/p/12071101.html
Copyright © 2020-2023  润新知