如何知道当前那个语句正在锁表？

如题

解决方案 »

  1.   

    show processlist;
    看state字段;
      

  2.   

    show processlist;只能看到有哪个语句在执行，不知道哪个锁住了表
    能再具体点么？
    另外我用 SHOW INNODB STATUS ;
    打印出来的信息看不明白
      

  3.   

    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 78675654
    Purge done for trx's n:o < 0 78675596 undo n:o < 0 0
    History list length 16
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 24312, OS thread id 1154599232
    MySQL thread id 208, query id 160539 localhost root
    SHOW INNODB STATUS
    ---TRANSACTION 0 78665336, not started, process no 24312, OS thread id 1154066752
    MySQL thread id 147, query id 140774 128.20.80.100 root
    ---TRANSACTION 0 78642823, not started, process no 24312, OS thread id 1153800512
    MySQL thread id 75, query id 75293 128.20.37.59 root
    ---TRANSACTION 0 0, not started, process no 24312, OS thread id 1153534272
    MySQL thread id 55, query id 72956 128.20.0.32 root
    ---TRANSACTION 0 0, not started, process no 24312, OS thread id 1153001792
    MySQL thread id 34, query id 159648 128.20.80.80 root
    ---TRANSACTION 0 78636962, not started, process no 24312, OS thread id 1152469312
    MySQL thread id 30, query id 159419 128.20.20.55 root
    ---TRANSACTION 0 78636299, not started, process no 24312, OS thread id 1152203072
    MySQL thread id 26, query id 159247 128.20.57.181 root
    ---TRANSACTION 0 78634959, not started, process no 24312, OS thread id 1151138112
    MySQL thread id 24, query id 57659 128.20.8.220 root
    ---TRANSACTION 0 78634809, not started, process no 24312, OS thread id 1151936832
    MySQL thread id 23, query id 159037 128.20.57.153 root
    ---TRANSACTION 0 78625859, not started, process no 24312, OS thread id 1151670592
    MySQL thread id 15, query id 160386 128.20.43.8 root
    ---TRANSACTION 0 78670293, not started, process no 24312, OS thread id 1151404352
    MySQL thread id 9, query id 154955 128.20.8.220 root
    ---TRANSACTION 0 78675559, not started, process no 24312, OS thread id 1150871872
    MySQL thread id 5, query id 160439 128.20.8.220 root
    ---TRANSACTION 0 78675622, not started, process no 24312, OS thread id 1150605632
    MySQL thread id 4, query id 160512 128.20.8.220 root
    ---TRANSACTION 0 78675644, not started, process no 24312, OS thread id 1150339392
    MySQL thread id 3, query id 160532 128.20.8.220 root
    ---TRANSACTION 0 78675653, not started, process no 24312, OS thread id 1149806912
    MySQL thread id 1, query id 160538 128.20.8.220 root
    ---TRANSACTION 0 78675602, ACTIVE 7 sec, process no 24312, OS thread id 1150073152 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 3 lock struct(s), heap size 368, 2 row lock(s)
    MySQL thread id 2, query id 160490 128.20.8.220 root Updating
    update tbl_reg_URL set sync = 0
    Trx read view will not see trx with id >= 0 78675603, sees < 0 78675543
    ------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 216 n bits 80 index `PRIMARY` of table `SIG_UCDB`.`tbl_reg_URL` trx id 0 78675602 lock_mode X waiting
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
     0: len 4; hex 00000008; asc     ;; 1: len 6; hex 000004b07e14; asc     ~ ;; 2: len 7; hex 000006c0023085; asc      0 ;; 3: len 2; hex 0001; asc   ;; 4: len 2; hex fffe; asc   ;; 5: len 1; hex 00; asc  ;; 6: len 4; hex 00000016; asc     ;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 10; hex 2a6c69616f312e636f6d; asc *liao1.com;;------------------
    ---TRANSACTION 0 78675544, ACTIVE 17 sec, process no 24312, OS thread id 1153268032 starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s)
    MySQL thread id 45, query id 160413 128.20.0.32 root Updating
    update tbl_user_define_URL set sync = 2 where id = 1528
    ------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 442427 n bits 464 index `PRIMARY` of table `SIG_UCDB`.`tbl_user_define_URL` trx id 0 78675544 lock_mode X locks rec but not gap waiting
    Record lock, heap no 387 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
     0: len 4; hex 000005f8; asc     ;; 1: len 6; hex 000004a700cf; asc       ;; 2: len 7; hex 0000074033335a; asc    @33Z;; 3: len 2; hex fffd; asc   ;; 4: len 2; hex 0001; asc   ;; 5: len 1; hex 00; asc  ;; 6: len 4; hex 00000000; asc     ;; 7: len 0; hex ; asc ;; 8: len 7; hex 3939352e636f6d; asc 995.com;;------------------
    ---TRANSACTION 0 78675543, ACTIVE 17 sec, process no 24312, OS thread id 1152735552
    7 lock struct(s), heap size 1216, 1112 row lock(s)
    MySQL thread id 33, query id 160410 128.20.0.32 root
    --------
      

  4.   

    我刚才测试了下 发现结果是这样的:
    在session1:
    执行:
    mysql> select CONNECTION_ID()
        -> ;
    +-----------------+
    | CONNECTION_ID() |
    +-----------------+
    |               1 |
    +-----------------+
    1 row in set (0.00 sec)mysql> use xx;
    Database changed
    mysql> set autocommit =0;
    Query OK, 0 rows affected (0.00 sec)mysql> select * from foo where str='bar1' for update;
    +----+------+
    | id | str  |
    +----+------+
    |  1 | bar1 |
    +----+------+
    1 row in set (0.05 sec)serssion 2:
    mysql> select CONNECTION_ID()
        -> ;
    +-----------------+
    | CONNECTION_ID() |
    +-----------------+
    |               2 |
    +-----------------+
    1 row in set (0.00 sec)mysql>  UPDATE foo SET str='bar';session3:
    mysql> select CONNECTION_ID()
        -> ;
    +-----------------+
    | CONNECTION_ID() |
    +-----------------+
    |               3 |
    +-----------------+
    1 row in set (0.00 sec)
    mysql> show processlist;
    +----+------+----------------+------+---------+------+----------+--------------
    -----------+
    | Id | User | Host           | db   | Command | Time | State    | Info
               |
    +----+------+----------------+------+---------+------+----------+--------------
    -----------+
    |  1 | root | localhost:3858 | xx   | Sleep   |   23 |          | NULL
               |
    |  2 | root | localhost:3859 | xx   | Query   |    7 | Updating | UPDATE foo SE
     str='bar' |
    |  3 | root | localhost:3860 | NULL | Query   |    0 | NULL     | show processl
    st         |
    +----+------+----------------+------+---------+------+----------+--------------
    -----------+show innodb status 如下:------------
    TRANSACTIONS
    ------------
    Trx id counter 0 1482499
    Purge done for trx's n:o < 0 1481530 undo n:o < 0 0
    History list length 3
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, OS thread id 4428
    MySQL thread id 4, query id 23 localhost 127.0.0.1 root
    show innodb STATUS
    ---TRANSACTION 0 1482498, ACTIVE 4 sec, OS thread id 4736 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 2, query id 22 localhost 127.0.0.1 root [color=#FF0000]Updating
    UPDATE foo SET str='bar'[/color]
    ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 4032 n bits 72 index `PRIMARY` of table `xx`.`foo` trx id 0 1482498 lock_mode X waiting
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
     0: len 4; hex 00000001; asc     ;; 1: len 6; hex 000000169b36; asc      6;; 2: len 7; hex 0000000fd92c4c; asc      ,L;; 3: len 4; hex 62617231; asc bar1;;
    楼主观察下;
    要是myisam的话 直接用show processlist 就可以了.如:mysql> show processlist;
    +----+------+----------------+------+---------+------+------------+-------------
    -----------------+
    | Id | User | Host           | db   | Command | Time | State      | Info
                     |
    +----+------+----------------+------+---------+------+------------+-------------
    -----------------+
    |  1 | root | localhost:3874 | xx   | Query   |   28 | User sleep | SELECT SLEEP
    (12345) FROM foo |
    |  2 | root | localhost:3879 | xx   | Query   |   19 | Locked     | UPDATE foo S
    ET str='bar'     |
    |  3 | root | localhost:3880 | NULL | Query   |    0 | NULL       | show process
    list             |
    +----+------+----------------+------+---------+------+------------+-------------
    -----------------+
    3 rows in set (0.00 sec)
      

  5.   

    在来自SHOW PROCESSLIST的输出中常见的一些状态：
    ·         Checking table 
    线程正在执行（自动）表格检查。
    ·         Closing tables 
    意味着线程正在刷新更改后的表数据，并正在关闭使用过的表。这应该是一个快速的操作。如果不快，则您应该验证您的磁盘没有充满，并且磁盘没有被超负荷使用。
    ·         Connect Out 
    连接到主服务器上的从属服务器。
    ·         Copying to tmp table on disk 
    临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器。
    ·         Creating tmp table 
    线程正在创建一个临时表，以保持部分结果。
    ·         deleting from main table 
    服务器正在执行多表删除的第一部分，只从第一个表中删除。
    ·         deleting from reference tables 
    服务器正在执行多表删除的第二部分，从其它表中删除匹配的行。
    ·         Flushing tables 
    线程正在执行FLUSH TABLES，并正在等待所有线程，以关闭表。
    ·         FULLTEXT initialization 
    服务器正在准备执行一个自然语言全文本搜索。
    ·         Killed 
    有人已经向线程发送了一个KILL命令。在下一次检查终止标记时，应放弃。该标记在MySQL的每个大循环中都检查，但是在有些情况下，线程终止只需要较短的时间。如果该线程被其它线程锁定，则只要其它线程接触锁定，终止操作就会生效。
    ·         Locked 
    该查询被其它查询锁定。
    ·         Sending data 
    线程正在为SELECT语句处理行，同时正在向客户端发送数据。
    ·         Sorting for group 
    线程正在进行分类，以满足GROUP BY要求。
    ·         Sorting for order 
    线程正在进行分类，以满足ORDER BY要求。
    ·         Opening tables 
    线程正在试图打开一个表。这应该是非常快的过程，除非打开操作受到阻止。例如，一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表，直到语句完成为止。
    ·         Removing duplicates 
    查询正在使用SELECT DISTINCT。使用时，在早期阶段，MySQL不能优化不同的操作。因此，MySQL要求一个额外的阶段，以便在把结果发送给客户端之前取消所有的复制行。
    ·         Reopen table 
    线程得到一个表锁定，但是在得到锁定后被通知带下方的表结构已更改了。它已经释放了锁定，关闭了表，并试图重新打开它。
    ·         Repair by sorting 
    修复代码正在使用一个分类来创建索引。
    ·         Repair with keycache 
    修复代码正在通过关键缓存一个接一个地使用创建关键字。这比通过分类修复要慢很多。
    ·         Searching rows for update 
    线程正在进行第一阶段，以在更新之前，查找所有匹配的行。如果UPDATE正在更改用于查找相关行的索引，则必须这么做。
    ·         Sleeping 
    线程正在等待客户端，以向它发送一个新语句。
    ·         System lock 
    线程正在等待得到一个用于表的外部系统锁定。如果您没有正在使用多个正在访问同一个表的mysqld服务器，则您可以使用--skip-external-locking选项禁用系统锁定。
    ·         Upgrading lock 
    INSERT DELAYED管理程序正在试图得到一个表锁定，以插入行。
    ·         Updating 
    线程正在搜索行，并正在更新这些行。
    ·         User Lock 
    线程正在等待GET_LOCK()。
    ·         Waiting for tables 
    线程得到一个通知，表的底层结构已经改变，需要重新打
      

  6.   

    mr_mablevi现在，我发现有两个lock，但是不知道是谁锁住了我要执行的那句话最郁闷的是，其中有一个lock，占用者就是我认为被锁住了那条语句。我现在是在一个事务里执行 batchUpdate，会不会个这个有关系？
    另外myisam是不是一下锁整个表
      

  7.   


    你说的对！Myisam是表锁，它会锁住整个表的所有记录innodb是行级锁，它只会锁当前处理的行记录。
      

  8.   

    myisam 是锁表的;
    innodb 有4个隔离级别;每个的作用都不一样,越高越安全,但是性能越差,锁在不通的隔离级别下面会有差别,
    楼主可以去看看手册中的:
    15.2.10. InnoDB事务模型和锁定
    .
      

  9.   

    我怎么看 我用的是myisam 还是innodb 
      

  10.   

    show table status 看第2列:engine 
      

  11.   

    update tbl_reg_URL set sync = 0update tbl_user_define_URL set sync = 2 where id = 1528从日志看是这2条语句执行的时候产生了死锁。
    但是这是UPDATE两个不同的表，为什么也会死锁呢？
      

  12.   

    是innodb的，也就是说是行级锁
    又纠结了，既然是行级锁，怎么会自己锁住自己呢我是在一个事务里面用batch update更新多行
      

  13.   

    不知道是不是死锁。
    update tbl_user_define_URL set sync = 2 where id = 1528不能获得锁，但是它想要的到的锁是被一条
    update tbl_user_define_URL set sync = 2 where id = 1528占用着的
      

  14.   


    从你的SHOW INNODB STATUS能够看到，是产生了死锁。
    你再贴一次最新的SHOW INNODB STATUS出来一起研究研究。
      

  15.   

    高性能mysql附录zuoxingyu不是有这书吗？show processlist只能显示等待进程说可以用mysqladmin debug 看，但innodb可能仍不能看高版本或许能看了
      

  16.   

    最近很忙，没空详细研究了，to be continued。
    先结贴了。另外我说的调整隔离级别不是在mysql里面做的，我是用的spring 的transaction manager，从serilizable调整到了read committed.
      

  17.   

    一般很少环境用到serilizable 隔离级别,一般用到第3个REPEATABLE-READ (默认)就可以了,或则用read-committed 也行.