转 How to Find Out Who Is Locking a Table in MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了转 How to Find Out Who Is Locking a Table in MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6451字，纯文字阅读大概需要10分钟。

内容图文

More importantly, the lines beginning “RECORD LOCKS space id 0” show which index of which table was locked. That is the real meat of the matter—that’s what you need to know.

There’s just one problem: after there’s been a deadlock, it’s too late. You don’t want to know what held locks in the past, you want to know what holds them now. The deadlock information isn’t usually helpful in finding out what transaction is blocking something from happening.

When a transaction is waiting for locks

The next place you can sometimes see lock information is in the transaction section of the output. Here’s a sample:

---TRANSACTION 0 93789802, ACTIVE 19 sec, process no 9544, OS thread id 389120018
MySQL thread id 23740, query id 194861248 worker1.office 192.168.0.12 robot
---TRANSACTION 0 93789797, ACTIVE 20 sec, process no 9537, OS thread id 389005359 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 23733, query id 194861215 elpaso 192.168.0.31 robot Updating
update test.test set col1 = 4
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 299998 n bits 200 index `PRIMARY` of table `test/test` trx id 0 93789797 lock_mode X locks rec but not gap waiting
Record lock, heap no 77 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 
 0: len 4; hex 80474fd6; asc  GO ;; 1: len 6; hex 000005970680; asc       ;; 2: len 7; hex 000017c02b176c; asc     + l;; 3: len 4; hex 80000003; asc     ;; 4: len 8; hex 800000000da0c93a; asc        :;; 5: len 8; hex 800000000eb2ea7e; asc        ~;; 6: len 4; hex c771fe44; asc  q D;; 7: len 4; hex 8000003e; asc    >;; 8: len 8; hex 8000123eb9e5dfd5; asc    >    ;; 9: len 4; hex 8000003a; asc    :;; 10: len 8; hex 8000123eb9e43603; asc    >  6 ;; 11: len 4; hex 80000035; asc    5;; 12: len 8; hex 8000123eb9d6c130; asc    >   0;; 13: len 4; hex 80000033; asc    3;; 14: len 8; hex 8000123eb9c7c853; asc    >   S;;
 
---------------------
---TRANSACTION 0 93789679, ACTIVE 31082 sec, process no 9535, OS thread id 388972583 starting index read, thread declared inside InnoDB 6
mysql tables in use 4, locked 4
11614 lock struct(s), heap size 683328
MySQL thread id 23731, query id 194861117 elpaso 192.168.0.31 robot

Notice the first transaction has been waiting 20 seconds for a lock to be granted, and it tells you which table and index as above. The other transaction I included (there were many in this section, but I omitted most) says it has 4 tables open and 4 locked. What it doesn’t say is which ones.

Again, there’s some information here, but not a lot. If you issue a query and it hangs and waits for a lock, knowing what lock it’s waiting for isn’t really helpful. And knowing some other transaction holds a lock isn’t always helpful either.

It can be useful sometimes though, and that’s better than nothing. If you only see two transactions with locks, you know the one that’s not waiting for a lock is probably the one that holds them. Notice something scary in the information above? Transaction “0 93789771”, on connection 23731, has been active for… eight and a half hours! Whoa. It’s time to find out what owns that connectionand possibly kill it.

The take-away here is, if you’re getting blocked on an InnoDB table, and you’re lucky enough to see only one other transaction with locks, it’s probably the one blocking you.

What about table locks?

Ah, good question. What if the table isn’t InnoDB, or what if someone locked it with LOCK TABLES, and it doesn’t show up in the output of SHOW ENGINE INNODB STATUS? As far as I know, you’re helpless. I don’t know how to get any information on who’s locking the table then. Table lock information doesn’t seem to be exposed in any fashion—only row lock information.

In fact, if you’re in a transaction, LOCK TABLES seems to “kick you out” of the transaction. Try experimenting with START TRANSACTION and LOCK TABLES on an InnoDB table, and you’ll see what I mean. If you lock a table for writing, then try to select from it in another connection, the other connection will block. If you then issue START TRANSACTION on the first connection, the second connection will immediately unblock, and the first connection’s transaction will disappear from the InnoDB status text.

Who wants to read all that mess?

Who, indeed? The text I included above is a pain to read, and it’s not even representative of what you’ll really be looking at. For one thing, you might have to scan through 40 or more transactions to find the ones you care about, and then there’s all the other information in the output, some of which can be voluminous (such as deadlocks). What a hassle!

Fortunately, there’s a tool to do that for you: innotop. This tool formats the output neatly and gives you filtering options to display only transactions with locks (or just sort them to the top of the display). Here’s how you can do that:

Start innotop and use the “T” key to enter InnoDB Transaction mode, if it’s not already in that mode. You will see a list of transactions. Next, make the “Locks,” “Tbl Used,” and “Tbl Lck” columns visible. Press the “c” key to activate the “choose columns” dialog.

Now sort transactions with locks to the top by pressing the “s” key and choosing lock_structs as the sort column. You may need to press the “r” key afterwards to reverse the sort order if they go to the bottom instead. Alternatively, you can use the “w” key to add a filter on the lock_structscolumn, such as “[1-9]” to match only rows where the column isn’t zero (this is a handy filter to add in general, just so you can see how many transactions have locks).

When you have a very busy server it can really help to hide all the transactions without locks.

Isn’t that easier than digging through the output of SHOW ENGINE INNODB STATUS? I think so.

Is there more?

Though I’ve searched the Internet, searched the source code and the MySQL manual, I haven’t been able to find any other ways to get information on current locks in MySQL. But I’d be delighted if you prove me wrong! If you have anything to add, please comment.

转 How to Find Out Who Is Locking a Table in MySQL

标签：ever   please   bee   info   active   scan   res   host   roo   

本文系统来源：https://www.cnblogs.com/feiyun8616/p/11517990.html

内容总结

以上是互联网集市为您收集整理的转 How to Find Out Who Is Locking a Table in MySQL全部内容，希望文章能够帮你解决转 How to Find Out Who Is Locking a Table in MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端