解决方案 »
- 如何在执行sql后的导出的文件里显示字段名(具体见内容)
- mysql dmp导入到mysql中
- 用MySQL.Data.dll操作MySQL数据库时,自动添加换行符“\”的问题
- 为什么这个库中要求所有字段都有内容才能插入新记录或更新?
- mysqld的问题
- 急寻InnoDB中文参考手册
- 如何在linux下实现对MySQL数据库实现定时自动备份(新手)-在线求助
- 菜鸟求救:我运行 ./mysqladmin -u root -p password '你的新密码'有问题
- 用Mysql编过桌面应用程序的兄弟们,你们的Mysql应用程序在启动时,是否一定要手动启动Mysql Server呢?我的应用程序为什么一启动就要我填写一个对话框,并启动Mysql Server,fo=
- mysql不能在存储过程或函数以外用if else等流程控制语句?
- 求教:一个MySQL查询语句如何实现?
- mysql cluster如何增加现有表空间大小
UPDATE itemgroup SET alarm_num=?, downline_times=?, fault_num=?, last_check_time=?, last_downline_time=?, last_notice_time=?, last_online_time=?, online_duration=? WHERE id=1对id为1的itemgroup更新死锁
两条update语句都有可能出现死锁现象,下位机的心跳update语句死锁更频繁些
一个事务只根据ID更新一个,下位机心跳更新和上位机监测更新的Update语句相同,只是更新内容不一样。
使用的Innodb引擎,缺省的锁超时时间是50秒
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 28, signal count 28
Mutex spin waits 0, rounds 352, OS waits 11
RW-shared spins 30, OS waits 15; RW-excl spins 4, OS waits 2
------------
TRANSACTIONS
------------
Trx id counter 0 1436831
Purge done for trx's n:o < 0 1436818 undo n:o < 0 0
History list length 26
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 4048
MySQL thread id 4, query id 8851 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 0, not started, OS thread id 2512
MySQL thread id 3, query id 8839 localhost 127.0.0.1 root
---TRANSACTION 0 1436830, ACTIVE 95 sec, OS thread id 2860
mysql tables in use 1, locked 1
MySQL thread id 16, query id 8849 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:58:07', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436829, ACTIVE 104 sec, OS thread id 1244
mysql tables in use 1, locked 1
MySQL thread id 9, query id 8823 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:56:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:58', online_duration=18642 where id=1
---TRANSACTION 0 1436828, ACTIVE 105 sec, OS thread id 4200
mysql tables in use 1, locked 1
MySQL thread id 18, query id 8810 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:57', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436827, ACTIVE 115 sec, OS thread id 4196
mysql tables in use 1, locked 1
MySQL thread id 17, query id 8793 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:47', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
---TRANSACTION 0 1436826, ACTIVE 125 sec, OS thread id 1572 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 7, query id 8753 localhost 127.0.0.1 root Updating
update itemgroup set alarm_num=0, downline_times=0, fault_num=0, last_check_time='2011-06-07 07:57:37', last_downline_time=null, last_notice_time=null, last_online_time='2011-06-07 07:57:07', online_duration=18642 where id=1
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1235 n bits 88 index `PRIMARY` of table `emview3_core_db`.`itemgroup` trx id 0 1436826 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000015ec96; asc ;; 2: len 7; hex 00000005421964; asc B d;; 3: len 7; hex 454d5332363030; asc EMS2600;; 4: len 13; hex 3139322e3136382e312e323030; asc 192.168.1.200;; 5: len 8; hex 8000124a5d8878e7; asc J] x ;; 6: len 12; hex c8f0beb0bce0bfd8d5beb5e3; asc ;; 7: len 8; hex 8000124a5d89b1f5; asc J] ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 80000000; asc ;; 10: len 1; hex 31; asc 1;; 11: len 4; hex 80000000; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 4; hex 80000000; asc ;; 14: len 8; hex 8000124a5d97497b; asc J] I{;; 15: SQL NULL; 16: SQL NULL; 17: len 8; hex 8000124a5d97497b; asc J] I{;; 18: len 8; hex 80000000000048d2; asc H ;; 19: len 4; hex 80000000; asc ;; 20: len 4; hex 80000001; asc ;; 21: len 4; hex 80000001; asc ;; 22: len 4; hex 80000001; asc ;; 23: len 4; hex 80000001; asc ;;------------------
---TRANSACTION 0 1436822, ACTIVE 155 sec, OS thread id 3604
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 2 row lock(s), undo log entries 2
MySQL thread id 5, query id 8813 localhost 127.0.0.1 root Table lock
update itemgroup set alarm_num=0, downline_times=1, fault_num=0, last_check_time='2011-06-07 07:57:07', last_downline_time='2011-06-07 07:55:28', last_notice_time=null, last_online_time='2011-06-06 18:48:21', online_duration=0 where id=3