Gisella, At 04:24 PM 10/31/01 -0800, you wrote: >Heikki, > >The output I get from the Monitor does not have the section > >-------------------------- >LOCKS HELD BY TRANSACTIONS >-------------------------- > >that I see in the manual (www.mysql.com/doc), section 7.5.9.1, The InnoDB >Monitor.
sorry, the manual lags behind the release. And 3.23.44 will again have an improved InnoDB monitor with somewhat different output. >I see two transactions issuing table locks to the same tables, >and within the same transactions, there are two locks in one table, >one for the table (IX, what kind of lock is this?), IX means intention exclusive. It means the transaction may have row level exclusive locks in the table (inserted rows, or updated rows, or X-locks set by SELECT FOR UPDATE). >and a record lock (X, exclusive?) for the secondary index?, X means exclusive yes. >where the second transaction shows, two identical record locks >for the index row? It can happen. >I went to your company's doc site, http://www.innodb.com/ibman.html >and also to the main page >and could not find some information about how to interpret the output. >For example, lock modes X, IX, S, ... >sync array info reservation count, signal count, etc. >and all short abbreviations. Sorry, the manual is not up-to-date, and much of that cryptic data is intended to myself for debugging purposes. It will take time to sieve the data which is useful for users. And of course there is the additional 1500 pages of documentation: the InnoDB source code :). >In the problem I have it seems that I get a table lock and then a record >lock on the same table, but on the index row. ?? > >---- > >When the test program I'm running gets the error 1000000, >this is part of what the monitor outputs: > >===================================== >011031 13:15:02 INNODB MONITOR OUTPUT >===================================== >------------ >TRANSACTIONS >------------ >Purge done for all trx's with n:o < 0 3033149, undo n:o < 0 0 >Total number of lock structs in row lock hash table 3 >---TRANSACTION 0 3033122, OS thread id 2032, not started, runs or sleeps >MySQL thread id 13, query id 143542 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033115, OS thread id 2020, not started, runs or sleeps >MySQL thread id 12, query id 143545 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033156, OS thread id 2060, not started, runs or sleeps >MySQL thread id 11, query id 143628 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033145, OS thread id 488, not started, runs or sleeps >MySQL thread id 10, query id 143632 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033148, OS thread id 1872, not started, runs or sleeps >MySQL thread id 9, query id 143619 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033131, OS thread id 1276, not started, runs or sleeps >MySQL thread id 7, query id 143551 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033116, OS thread id 1256, not started, runs or sleeps >MySQL thread id 5, query id 143587 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033151, OS thread id 1428, not started, runs or sleeps >MySQL thread id 4, query id 143621 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 2956333, OS thread id 1956, not started, runs or sleeps >MySQL thread id 3, query id 13 PCATEST3 192.168.1.113 PCAServer >---TRANSACTION 0 3033146, OS thread id 1548, active, runs or sleeps, has 3 >lock struct(s), undo log entries 1 >MySQL thread id 6, query id 143631 PCATEST3 192.168.1.113 PCAServer >Trx read view will not see trx with id >= 0 3033147, sees < 0 3033144 >TABLE LOCK table MyPCAServer/payinst trx id 0 3033146 lock_mode IX >TABLE LOCK table MyPCAServer/walletpayinst trx id 0 3033146 lock_mode IX >RECORD LOCKS space id 0 page no 298 n bits 672 table >MyPCAServer/walletpayinst index idx_walletpayinst_payiindex trx id 0 3033146 >lock_mode X >---TRANSACTION 0 3033144, OS thread id 1980, active, runs or sleeps, has 4 >lock struct(s), undo log entries 1 >MySQL thread id 8, query id 143630 PCATEST3 192.168.1.113 PCAServer >Trx read view will not see trx with id >= 0 3033145, sees < 0 3033145 >TABLE LOCK table MyPCAServer/payinst trx id 0 3033144 lock_mode IX >TABLE LOCK table MyPCAServer/walletpayinst trx id 0 3033144 lock_mode IX >RECORD LOCKS space id 0 page no 298 n bits 672 table >MyPCAServer/walletpayinst index idx_walletpayinst_payiindex trx id 0 3033144 >lock_mode X >Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; >asc supremum;; >RECORD LOCKS space id 0 page no 298 n bits 672 table >MyPCAServer/walletpayinst index idx_walletpayinst_payiindex trx id 0 3033144 >lock_mode X Ok, if the deadlocks come from the tables payinst and walletpayinst, then it looks like there are are record lock waits in the index idx_walletpayinst_payiindex A trx has an X lock on the "supremum" record of a page. That might be the high end of the index. The next step is for you to look what SQL statements access these tables, what indexes they use, and what kind of operations they do. Especially, do you use SELECT ... FOR UPDATE or UPDATEs, which will touch the index idx_walletpayinst_payiindex? Do you insert then rows which create records to the locked areas of idx_walletpayinst_payiindex? Is the hotspot in idx_walletpayinst_payiindex the high end of the index? A simple way to eliminate the deadlocks here is the "semaphore row" method I suggested yesterday. You create a "semaphore" table with a single row. Before accessing walletpayinst and payinst, you make your transactions to update the "semaphore" row. They thus get an X-lock on the "semaphore" row, which serializes your transactions, and deadlocks are avoided. Another possibility is that you use LOCK TABLES at the start of transactions. Note that MySQL requires you to lock all tables which you access in the transaction, and in the same LOCK TABLES statement. LOCK TABLES and UNLOCK TABLES are kind of trx start, trx end brackets in MySQL, and they also commit the current transaction. >Regards, > >Gisella Regards, Heikki http://www.innodb.com/ibman.html --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
