表锁命令
UPDATE terminal_addtion tt SET tt.al_level = 2 where tt.last_time>=date_add(now(),interval -2 hour); 改造后的EXPLAIN信息mysql> explain select tt.al_level from terminal_addtion tt where tt.last_time>=date_add(now(),interval -2 hour);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tt | ALL | idx_last_time | NULL | NULL | NULL | 1831 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)mysql>
索引情况mysql> show index from terminal_addtion;
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
| terminal_addtion | 0 | PRIMARY | 1 | terminal_addtion_id | A | 1831 | NULL | NULL | |
BTREE | |
| terminal_addtion | 1 | FK533390722BA56BF4 | 1 | terminal_id | A | 1831 | NULL | NULL | YES |
BTREE | |
| terminal_addtion | 1 | idx_last_time | 1 | last_time | A | 1831 | NULL | NULL | |
BTREE | |
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
3 rows in set (0.03 sec)mysql>
由于这个表锁,导致terminal_addtion 被死锁了。
有什么办法使上面的UPDATA行锁?
UPDATE terminal_addtion tt SET tt.al_level = 2 where tt.last_time>=date_add(now(),interval -2 hour); 改造后的EXPLAIN信息mysql> explain select tt.al_level from terminal_addtion tt where tt.last_time>=date_add(now(),interval -2 hour);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tt | ALL | idx_last_time | NULL | NULL | NULL | 1831 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)mysql>
索引情况mysql> show index from terminal_addtion;
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
| terminal_addtion | 0 | PRIMARY | 1 | terminal_addtion_id | A | 1831 | NULL | NULL | |
BTREE | |
| terminal_addtion | 1 | FK533390722BA56BF4 | 1 | terminal_id | A | 1831 | NULL | NULL | YES |
BTREE | |
| terminal_addtion | 1 | idx_last_time | 1 | last_time | A | 1831 | NULL | NULL | |
BTREE | |
+------------------+------------+--------------------+--------------+---------------------+-----------+-------------+----------+--------+------+-
-----------+---------+
3 rows in set (0.03 sec)mysql>
由于这个表锁,导致terminal_addtion 被死锁了。
有什么办法使上面的UPDATA行锁?
INNODB引擎为行级锁,容易出现死锁
WWWA的意思是:
start TRANSACTION;
UPDATE terminal_addtion tt SET tt.al_level = 2 where tt.last_time>=date_add(now(),interval -2 hour);
COMMIT;
这样包裹起来,是么?
服务重启后开始是没有锁表的,但是表竞争很厉害,过一段时间后就出现表死锁了。mysql> show global status like '%lock%';
+-------------------------------+---------+
| Variable_name | Value |
+-------------------------------+---------+
| Com_lock_tables | 0 |
| Com_unlock_tables | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 33953 |
| Innodb_row_lock_time_avg | 29 |
| Innodb_row_lock_time_max | 10187 |
| Innodb_row_lock_waits | 1161 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 343283 |
| Key_blocks_used | 3308 |
| Qcache_free_blocks | 26 |
| Qcache_total_blocks | 660 |
| Table_locks_immediate | 1668094 |
| Table_locks_waited | 1 |
+-------------------------------+---------+
14 rows in set (0.02 sec)mysql>
| Innodb_row_lock_time_avg | 29 |
这个数29毫秒,
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
我在查看show processlist 的时候看不到锁住的链接,是不是因为50毫秒就死锁超时了,所以看不到啊?
mysql> show tables like '%monitor%';
+---------------------------+
| Tables_in_cpc (%monitor%) |
+---------------------------+
| innodb_monitor |
+---------------------------+
1 row in set (0.06 sec)mysql> use mysql;
Database changed
mysql> show tables like '%monitor%';
+-----------------------------+
| Tables_in_mysql (%monitor%) |
+-----------------------------+
| innodb_monitor |
+-----------------------------+
1 row in set (0.01 sec)mysql>
我打开了MONITOR,但是错误日志里面却没有死锁信息。
SHOW INNODB STATUS能够看到死锁信息,。。这又是咋回事啊??狼头哥,快现身吧。
------------------------
110106 4:24:48
*** (1) TRANSACTION:
TRANSACTION 0 58581738, ACTIVE 1 sec, OS thread id 392 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 2496, 1604 row lock(s)
MySQL thread id 407, query id 5340103 localhost 127.0.0.1 root Updating
UPDATE terminal_addtion SET IS_ALARM_RESUME= NAME_CONST('vids',_utf8'1000000000' COLLATE 'utf8_general_ci'),grande= NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,-1,-1,-1' COLLATE 'utf8_general_ci'),
al_level= if(intermit=1,4,
case
when FIND_IN_SET('4', NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,-1,-1,-1' COLLATE 'utf8_general_ci')) then 4
when FIND_IN_SET('3', NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,-1,-1,-1' COLLATE 'utf8_general_ci')) then 3
when FIND_IN_SET('2', NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,-1,-1,-1' COLLATE 'utf8_general_ci')) then 2
when FIND_IN_SET('1', NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,-1,-1,-1' COLLATE 'utf8_general_ci')) then 1
when FIND_IN_SET('0', NAME_CONST('vtgrande',_utf8'4,-1,-1,-1,-1,-1,-1,
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1159 page no 11 n bits 208 index `PRIMARY` of table `cpc`.`terminal_addtion` trx id 0 58581738 lock_mode X locks rec but not gap waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
0: len 4; hex 8000030b; asc ;; 1: len 6; hex 0000037de29a; asc } ;; 2: len 7; hex 000000003201e3; asc 2 ;; 3: len 4; hex 8000035c; asc \;; 4: len 8; hex 8000124a3fba1ff5; asc J? ;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 10; hex 31303030303030303030; asc 1000000000;; 9: len 4; hex 80000004; asc ;; 10: len 11; hex 3133313931303431313239; asc 13191041129;; 11: len 0; hex ; asc ;; 12: len 4; hex 800002fc; asc ;; 13: len 8; hex 8000000000000468; asc h;; 14: len 28; hex 342c2d312c2d312c2d312c2d312c2d312c2d312c2d312c2d312c2d31; asc 4,-1,-1,-1,-1,-1,-1,-1,-1,-1;;*** (2) TRANSACTION:
TRANSACTION 0 58581739, ACTIVE 1 sec, OS thread id 12476 fetching rows, thread declared inside InnoDB 340
mysql tables in use 1, locked 1
24 lock struct(s), heap size 2496, 2464 row lock(s), undo log entries 441
MySQL thread id 413, query id 5340027 localhost 127.0.0.1 root Updating
UPDATE terminal_addtion tt SET tt.al_level = (case
when FIND_IN_SET('4',tt.grande) then 4
when FIND_IN_SET('3',tt.grande) then 3
when FIND_IN_SET('2',tt.grande) then 2
when FIND_IN_SET('1',tt.grande) then 1
when FIND_IN_SET('0',tt.grande) then 0
else -1
end )
where tt.last_time>=date_add( NAME_CONST('cdate',_binary'2011-01-06 04:24:47' COLLATE 'binary') ,interval - NAME_CONST('hours',4) hour)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1159 page no 11 n bits 208 index `PRIMARY` of table `cpc`.`terminal_addtion` trx id 0 58581739 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 LOCKS space id 1159 page no 11 n bits 208 index `PRIMARY` of table `cpc`.`terminal_addtion` trx id 0 58581739 lock mode S
when FIND_IN_SET('4',tt.grande) then 4
when FIND_IN_SET('3',tt.grande) then 3
when FIND_IN_SET('2',tt.grande) then 2
when FIND_IN_SET('1',tt.grande) then 1
when FIND_IN_SET('0',tt.grande) then 0
else -1
end )
where terminal_addtion_id in(
select terminal_addtion_id from terminal_addtion
where last_time>=date_add( NAME_CONST('cdate',_binary'2011-01-06 04:24:47' COLLATE 'binary') ,interval - NAME_CONST('hours',4) hour)) 这样试试
这样的语句我转换成EXPLAIN查看执行计划,发现这个表还是全表扫描的。
T1一次只修改一条记录,T2一次修改多条记录。
回版主,没有测试18楼的语句。现在我用START TRANSACTION, COMMIT把UPDATE包起来,暂时没有出现死锁。