Chinaunix首页 | 论坛 | 博客
  • 博客访问: 44915
  • 博文数量: 15
  • 博客积分: 51
  • 博客等级: 民兵
  • 技术积分: 155
  • 用 户 组: 普通用户
  • 注册时间: 2010-03-06 22:29
个人简介

mysql dba

文章分类
文章存档

2016年(5)

2015年(9)

2013年(1)

我的朋友

分类: Mysql/postgreSQL

2016-12-20 23:33:36


  1. "root@localhost:mysql9129.sock [china9129]>\s
  2. --------------
  3. mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper #mysql版本 5.7.16

  4. Connection id: 10042
  5. Current database: china9129
  6. Current user: root@localhost
  7. SSL: Not in use
  8. Current pager: stdout
  9. Using outfile: ''
  10. Using delimiter: ;
  11. Server version: 5.7.16-log MySQL Community Server (GPL)
  12. Protocol version: 10
  13. Connection: Localhost via UNIX socket
  14. Server characterset: utf8
  15. Db characterset: utf8
  16. Client characterset: utf8
  17. Conn. characterset: utf8
  18. UNIX socket: /data/mysql/mysql9129/sock/mysql9129.sock
  19. Uptime: 4 days 6 hours 2 min 31 sec


  20. Threads: 4 Questions: 31133 Slow queries: 0 Opens: 254 Flush tables: 3 Open tables: 58 Queries per second avg: 0.084


  1. SESSION 1:
  2. "root@localhost:mysql9129.sock [(none)]>set global TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  3. "root@localhost:mysql9129.sock [(none)]>select @@tx_isolation;
  4. +-----------------+
  5. | @@tx_isolation |
  6. +-----------------+
  7. | REPEATABLE-READ |
  8. +-----------------+
  9. 1 row in set (0.00 sec)

  10. "root@localhost:mysql9129.sock [china9129]>show create table t100;
  11. +-------+----------------------------------------------------------------------------------------------------------+
  12. | Table | Create Table |
  13. +-------+----------------------------------------------------------------------------------------------------------+
  14. | t100 | CREATE TABLE `t100` (
  15.   `id` int(11) NOT NULL,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  18. +-------+----------------------------------------------------------------------------------------------------------+
  19. 1 row in set (0.00 sec)

  20. "root@localhost:mysql9129.sock [china9129]>select * from t100;
  21. +-----+
  22. | id |
  23. +-----+
  24. | 1 |
  25. | 2 |
  26. | 3 |
  27. | 4 |
  28. | 5 |
  29. | 111 |
  30. +-----+

  31. "root@localhost:mysql9129.sock [china9129]>begin;
  32. Query OK, 0 rows affected (0.00 sec)

  33. "root@localhost:mysql9129.sock [china9129]>select * from t100 where id=5 for update;
  34. +----+
  35. | id |
  36. +----+
  37. | 5 |
  38. +----+
  39. 1 row in set (0.00 sec)

  40. "root@localhost:mysql9129.sock [china9129]>delete from t100 where id =3;
  41. Query OK, 1 row affected (3.62 sec)
  42. ==========================================================================================================
  43. SESSION 2:

  44. "root@localhost:mysql9129.sock [(none)]>begin;
  45. Query OK, 0 rows affected (0.00 sec)

  46. "root@localhost:mysql9129.sock [(none)]>use china9129;
  47. Database changed
  48. "root@localhost:mysql9129.sock [china9129]>select * from t100 where id=3 for update;
  49. +----+
  50. | id |
  51. +----+
  52. | 3 |
  53. +----+
  54. 1 row in set (0.00 sec)

  55. "root@localhost:mysql9129.sock [china9129]>delete from t100 where id =5;
  56. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

  1. #查看死锁信息
  2. "root@localhost:mysql9129.sock [china9129]>show engine innodb status \G
  3. *************************** 1. row ***************************
  4.   Type: InnoDB
  5.   Name:
  6. Status:
  7. =====================================
  8. 2016-12-20 21:57:45 0x7fa0bffff700 INNODB MONITOR OUTPUT
  9. =====================================
  10. Per second averages calculated from the last 24 seconds
  11. -----------------
  12. BACKGROUND THREAD
  13. -----------------
  14. srv_master_thread loops: 1133 srv_active, 0 srv_shutdown, 362529 srv_idle
  15. srv_master_thread log flush and writes: 363662
  16. ----------
  17. SEMAPHORES
  18. ----------
  19. OS WAIT ARRAY INFO: reservation count 4540
  20. OS WAIT ARRAY INFO: signal count 5766
  21. RW-shared spins 0, rounds 4683, OS waits 474
  22. RW-excl spins 0, rounds 134731, OS waits 1327
  23. RW-sx spins 6332, rounds 138603, OS waits 2382
  24. Spin rounds per wait: 4683.00 RW-shared, 134731.00 RW-excl, 21.89 RW-sx
  25. ------------------------
  26. LATEST DETECTED DEADLOCK #监测出最近的死锁信息
  27. ------------------------
  28. 2016-12-20 21:56:29 0x7fa0bffff700
  29. *** (1) TRANSACTION: #第一个事物
  30. TRANSACTION 13899, ACTIVE 51 sec starting index read #事物id 13899,活跃了51秒
  31. mysql tables in use 1, locked 1 #mysql有一个表在使用,一个表被锁住
  32. LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) #有3个锁链表,内存中堆的大小1136, 两行记录被锁定
  33. MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root updating
  34.  #mysql线程id 10041, query id 31128, localhost root用户执行update操作
  35. delete from t100 where id =3 #执行了这个SQL语句的时候,发生了锁等待
  36. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: #等待这个锁被释放
  37. RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13899 lock_mode X locks rec but not gap waiting
  38. #类型:行锁,等待在t100的主键上 page num 3,加一个X锁(not gap waiting),锁住80 bits。
  39. Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  40.  0: len 4; hex 80000003; asc ;;
  41.  1: len 6; hex 00000000363b; asc 6;;;
  42.  2: len 7; hex c7000001660110; asc f ;;


  43. *** (2) TRANSACTION:#第二个事物
  44. TRANSACTION 13900, ACTIVE 31 sec starting index read #事物id 13900,活跃了31秒
  45. mysql tables in use 1, locked 1 #mysql有一个表在使用,一个表被锁住
  46. 3 lock struct(s), heap size 1136, 2 row lock(s) #有3个锁链表,内存中堆的大小1136, 两行记录被锁定
  47. MySQL thread id 10042, OS thread handle 140328392718080, query id 31129 localhost root updating 
  48.  #mysql线程id 10042, query id 31129, localhost root用户执行update操作
  49. delete from t100 where id =5 #执行了这个SQL语句的时候,发生了锁等待
  50. *** (2) HOLDS THE LOCK(S): #事物 13900 持有的锁
  51. RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap
  52. #类型:行锁,事物 id 13900,在t100的主键上 page num 3,加一个X锁(not gap 没有间隙锁),锁住80 bits
  53. Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  54.  0: len 4; hex 80000003; asc ;;
  55.  1: len 6; hex 00000000363b; asc 6;;;
  56.  2: len 7; hex c7000001660110; asc f ;;


  57. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: #当事物2在执行delete from t100 where id =5的时候,发生锁等待
  58. RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap waiting
  59. #类型:行锁,等待在t100的主键上 page num 3,加一个X锁(not gap waiting 没有间隙锁),锁住80 bits。
  60. Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  61.  0: len 4; hex 80000005; asc ;;
  62.  1: len 6; hex 00000000363d; asc 6=;;
  63.  2: len 7; hex c90000014c0110; asc L ;;


  64. *** WE ROLL BACK TRANSACTION (2) 
  65. #事物2,TRANSACTION 13900被回滚了。报错信息:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  66. ------------
  67. TRANSACTIONS
  68. ------------
  69. Trx id counter 13905
  70. Purge done for trx's n:o < 13905 undo n:o < 0 state: running but idle
  71. History list length 568
  72. LIST OF TRANSACTIONS FOR EACH SESSION:
  73. ---TRANSACTION 421807334705888, not started
  74. 0 lock struct(s), heap size 1136, 0 row lock(s)
  75. ---TRANSACTION 13899, ACTIVE 127 sec
  76. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  77. MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root cleaning up
  78. -------


  1. 备注:
  2. (1) 在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一 条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。
  3.     在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking
  4. (2) 把死锁信息写入到error.log。
  5.     set global innodb_print_all_deadlocks = 1;


阅读(525) | 评论(0) | 转发(0) |
0

上一篇:使用 linux 下的 TC 流量控制测试

下一篇:没有了

给主人留下些什么吧!~~