Chinaunix首页 | 论坛 | 博客
  • 博客访问: 479917
  • 博文数量: 99
  • 博客积分: 3621
  • 博客等级: 中校
  • 技术积分: 1089
  • 用 户 组: 普通用户
  • 注册时间: 2010-06-22 16:29
文章存档

2012年(21)

2011年(28)

2010年(50)

分类: Mysql/postgreSQL

2012-06-12 10:08:28

show engine innodb status\G

点击(此处)折叠或打开

  1. *** (1) TRANSACTION:
  2. TRANSACTION 8631FDC, ACTIVE 0 sec inserting
  3. mysql tables in use 2, locked 2
  4. LOCK WAIT 7 lock struct(s), heap size 1248, 107 row lock(s)
  5. MySQL thread id 28162, OS thread handle 0x7f4ab3073700, query id 210340529 tc-demo-dev3.dfengg.com 172.16.2.173 root
  6. INSERT INTO Text****(BusinessId,Type,Template,CreatedUserId,LastModifiedUserId,Delivery,DeliveryWindow,BusinessCalendar) SELECT 124001698, 205, '[CustomerFirstName], your next appointment with [Business] is [AppointmentDateTime]. We look forward to seeing you at your upcoming appointment.', 0, 0, IFNULL(96, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=205
  7. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  8. RECORD LOCKS space id 794567 page no 6 n bits 88 index `PRIMARY` of table `df1`.`Text****` trx id 8631FDC lock_mode X insert intention waiting
  9. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
  10.  0: len 8; hex 73757072656d756d; asc supremum;;

  11. *** (2) TRANSACTION:
  12. TRANSACTION 8631FDB, ACTIVE 0 sec setting auto-inc lock
  13. mysql tables in use 2, locked 2
  14. 5 lock struct(s), heap size 1248, 106 row lock(s)
  15. MySQL thread id 28158, OS thread handle 0x7f4a48cd0700, query id 210340528 tc-demo-dev3.dfengg.com 172.16.2.173 root
  16. INSERT INTO Text****(BusinessId,Type,Template,CreatedUserId,LastModifiedUserId,Delivery,DeliveryWindow,BusinessCalendar) SELECT 124001699, 406, 'Dear [CustomerFirstName],\n\nYou have an upcoming appointment with [BusinessName]. Please take a minute to review the details of your appointment scheduled for:\n\n[AppointmentDateTime]\n\nIf you are unable to make this appointment, it is important that you call us at [BusinessPhone] as soon as possible so we can make other arrangements.\n\nWe look forward to seeing you at your upcoming appointment.\n\nSincerely,\n\n[BusinessName]\n[BusinessPhone]\n[BusinessEmail]\n[BusinessWebsite]\n\n-----\nThis email provided by Demandforce, Inc. To unsubscribe, please click this link:\n\n[UnsubscribeLink]\n\n', 0, 0, IFNULL(504, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=406
  17. *** (2) HOLDS THE LOCK(S):
  18. RECORD LOCKS space id 794567 page no 6 n bits 88 index `PRIMARY` of table `df1`.`Text****` trx id 8631FDB lock mode S
  19. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

  20. .................................

  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. TABLE LOCK table `df1`.`Text****` trx id 8631FDB lock mode AUTO-INC waiting
  23. *** WE ROLL BACK TRANSACTION (2)
分析:
1)insert .... select ...语句,在MYSQL里,会对select ...涉及的所有记录进行锁定,这是特殊的select加X锁的情况,原因是为保证数据的一致性(M-S环境),假设不锁定,在执行过程中,select包含的表一直在做插入操作,那么M端select出来的记录数就会比S端少,数据就会不一致。
 
2)查看select的执行计划

点击(此处)折叠或打开

  1. mysql> explain SELECT 124001698, 205, '[CustomerFirstName], your next appointment with [Business] is [AppointmentDateTime]. We look forward to seeing you at your upcoming appointment.', 0, 0, IFNULL(96, Delivery),DeliveryWindow,BusinessCalendar FROM Text**** WHERE BusinessId=0 AND Type=205;
  2. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
  5. | 1 | SIMPLE | Text**** | ALL | NULL | NULL | NULL | NULL | 6108 | Using where |
  6. +----+-------------+---------------------+------+---------------+------+---------+------+------+-------------+
  7. 1 row in set (0.00 sec)
key=null,rows=6108
全表扫描,没有可用的索引。
 
解决办法:
在BusinessId,Type字段建复合索引。
 
通过索引1)可以更快的定位数据2)锁定更少的行。
 

 
 
 

 
阅读(5229) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~