Re: How could I know which transaction or thread hold the lock
Sheeri, Gu Lei, SHOW PROCESSLIST only knows about MySQL table locks. To list row locks, you need to use innodb_lock_monitor: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, April 07, 2006 10:23 PM Subject: Re: How could I know which transaction or thread hold the lock While the query is still running, type mysql show processlist or mysql show full processlist find the query(ies) with the State column having a value of Locked -Sheeri On 4/7/06, $B8EMk(B [EMAIL PROTECTED] wrote: Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How could I know which transaction or thread hold the lock
While the query is still running, type mysql show processlist or mysql show full processlist find the query(ies) with the State column having a value of Locked -Sheeri On 4/7/06, 古雷 [EMAIL PROTECTED] wrote: Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How could I know which transaction or thread hold the lock
Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei
