表锁命令
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行锁?

解决方案 »

  1.   

    数据量多大,
    INNODB引擎为行级锁,容易出现死锁
      

  2.   

    我这个语句是写在存储过程里的。
    WWWA的意思是:
    start TRANSACTION;
    UPDATE terminal_addtion tt SET tt.al_level = 2 where tt.last_time>=date_add(now(),interval -2 hour); 
    COMMIT;
    这样包裹起来,是么?
      

  3.   


    服务重启后开始是没有锁表的,但是表竞争很厉害,过一段时间后就出现表死锁了。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>
      

  4.   

    用户也不对。
    | Innodb_row_lock_time_avg      | 29      |
    这个数29毫秒,
    | innodb_lock_wait_timeout   | 50                |
    | innodb_rollback_on_timeout | OFF               |
    我在查看show processlist 的时候看不到锁住的链接,是不是因为50毫秒就死锁超时了,所以看不到啊?
      

  5.   


    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能够看到死锁信息,。。这又是咋回事啊??狼头哥,快现身吧。
      

  6.   

    LATEST DETECTED DEADLOCK
    ------------------------
    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;;锁信息贴出来
      

  7.   

    innodb引擎,直接使用事务语句呢?看看什么结果。
      

  8.   

    检查 innodb_table_locks、autocommit 设置
    粗略看了一下,是锁住了索引?
    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
      

  9.   

    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 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)) 这样试试
      

  10.   

    terminal_addtion 中有多少记录?只有1831条?
      

  11.   


    这样的语句我转换成EXPLAIN查看执行计划,发现这个表还是全表扫描的。
    T1一次只修改一条记录,T2一次修改多条记录。
      

  12.   


    回版主,没有测试18楼的语句。现在我用START TRANSACTION, COMMIT把UPDATE包起来,暂时没有出现死锁。
      

  13.   

    我记得对于MYSQL的事务好像有这么个说法:事务包裹操作UPDATE的表,都会加入一个全局的X锁。