mysql死锁几种情况的测试_MySQL_MySQL_青云站长教程网
欢迎来到站长教程网!
  • 秒到短信接口 免费试用
  • 微信支付宝接口 秒结算
  • 中文网站排名|申请加入
  • 搜索引擎全站查询
  • 查询移动网站权重数据
  • 海外主机 台湾公司直销
  • MySQL

    当前位置:主页 > 数据库 > MySQL >

    mysql死锁几种情况的测试_MySQL

    时间:2019-11-09|栏目:MySQL|点击:
  • sessionA:
    test>begin
    -> ;
    Query OK, 0 rows affected (0.00 sec)

    test>select * from tt where id_test=1234 lock in share mode;
    +—-+———+
    | id | id_test |
    +—-+———+
    | 4 | 1234 |
    +—-+———+
    1 row in set (0.01 sec)
    sessionB:
    test>delete from tt where id_test=1234;
    卡住
    sessionA:
    test>delete from tt where id_test=1234;
    Query OK, 1 row affected (0.00 sec)
    sessionb被杀掉,a执行
    查看锁信息
    (none)>show engine innodb status\G
    ***************** 1. row *****************
    Type: InnoDB
    Name:

    Status:2016-05-09 16:26:27 7f8ee2123700 INNODB MONITOR OUTPUTPer second averages calculated from the last 18 secondsBACKGROUND THREAD

    srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 191469 srv_idle

    srv_master_thread log flush and writes: 191478SEMAPHORES

    OS WAIT ARRAY INFO: reservation count 23
    OS WAIT ARRAY INFO: signal count 23
    Mutex spin waits 16, rounds 210, OS waits 7
    RW-shared spins 16, rounds 480, OS waits 16
    RW-excl spins 0, rounds 0, OS waits 0

    Spin rounds per wait: 13.12 mutex, 30.00 RW-shared, 0.00 RW-exclLATEST DETECTED DEADLOCK

    2016-05-09 15:56:30 7f8ee2154700
    * (1) TRANSACTION:
    TRANSACTION 4396, ACTIVE 11 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
    MySQL thread id 4, OS thread handle 0x7f8ee2123700, query id 52 192.168.90.109 myadmin updating
    delete from tt where id_test=1234
    * (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4396 lock_mode X waiting
    Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000001; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 13000001460476; asc F v;;
    3: len 4; hex 800003e8; asc ;;

    * (2) TRANSACTION:
    TRANSACTION 4395, ACTIVE 30 sec starting index read, thread declared inside InnoDB 5000
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1184, 7 row lock(s)
    MySQL thread id 3, OS thread handle 0x7f8ee2154700, query id 53 192.168.90.109 myadmin updating
    delete from tt where id_test=1234
    * (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4395 lock mode S
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    0: len 8; hex 73757072656d756d; asc supremum;;

    Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000001; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 13000001460476; asc F v;;
    3: len 4; hex 800003e8; asc ;;

    Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000002; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 1300000146049b; asc F ;;
    3: len 4; hex 800003e8; asc ;;

    Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000003; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 130000014604c0; asc F ;;
    3: len 4; hex 800003e8; asc ;;

    Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000004; asc ;;
    1: len 6; hex 000000001129; asc );;
    2: len 7; hex 1d000001d202df; asc ;;
    3: len 4; hex 800004d2; asc ;;

    Record lock, heap no 15 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000006; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 1300000146052f; asc F /;;
    3: len 4; hex 800003e8; asc ;;

    * (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4395 lock_mode X waiting
    Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
    0: len 8; hex 8000000000000001; asc ;;
    1: len 6; hex 000000001117; asc ;;
    2: len 7; hex 13000001460476; asc F v;;
    3: len 4; hex 800003e8; asc ;;

    * WE ROLL BACK TRANSACTION (1)

    a持有s锁,b执行删除操作请求x,但sx互斥,b进入请求队列等待,a在请求x锁,这个时候队列中b在排队,还轮不上a,a就等待,这种循环等待出现,死锁就出现了。

    下面是oracle中经常出现的场景
    sessionA
    test>select * from tt;
    +—-+———+
    | id | id_test |
    +—-+———+
    | 1 | 1000 |
    | 3 | 1000 |
    | 4 | 1234 |
    | 6 | 1000 |
    +—-+———+
    4 rows in set (0.00 sec)

    select * from t7;
    +—-+——+
    | id | name |
    +—-+——+
    | 1 | aa |
    +—-+——+
    1 row in set (0.01 sec)

    .test>begin;
    Query OK, 0 rows affected (0.00 sec)
    先删除7中的id=1,sessionb中删除ttid=1

    上一篇:MySQL--编写脚本_MySQL

    栏    目:MySQL

    下一篇:怎样在 Oracle RAC 环境下使用 Parallel 参数提高 Data Pump job

    本文标题:mysql死锁几种情况的测试_MySQL

    本文地址:http://www.jh-floor.com/shujuku/MySQL/123585.html

    您可能感兴趣的文章

    广告投放 | 联系我们 | 版权申明

    重要申明:本站所有的文章、图片、评论等,均由网友发表或上传并维护或收集自网络,属个人行为,与本站立场无关。

    如果侵犯了您的权利,请与我们联系,我们将在24小时内进行处理、任何非本站因素导致的法律后果,本站均不负任何责任。

    联系QQ:888888 | 邮箱:888888#qq.com(#换成@)

    Copyright © 2002-2017 青云站长教程网 版权所有 琼ICP备xxxxxxxx号