MySQL Forums
Forum List  »  InnoDB

innodb update locks table...
Posted by: Anda Zhao
Date: February 26, 2006 04:23PM

Hi,

I am fairly new to MySQL and I am having some problem with MySQL using innodb. We have an application that will do very simple select/insert/update/delete. Each time the query only affects one row at a time:

Here is the table structure:

+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| MIN | varchar(30) | NO | PRI | | |
| OBJECT_DATA | longblob | YES | | NULL | |
| DATE_CREATED | datetime | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+

And here are the quesies:

select object_data from x where min = ?;
update x set object_data = ? where min = ?;
insert into x values (?, ?, now());
delete from x where min = ?;

we have auto_commit set to true in the application.

We are using connector/J with tomcate DBCP connection pooing and here is the setting in server.xml:

<Resource name="jdbc/mysqlds" auth="Container" type="javax.sql.DataSource" />
<ResourceParams name="jdbc/mysqlrts">
<parameter>
<name>factory</name>
<value>com.televigation.db.TeleNavDatasourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://mysqlserver:3306/rts</value>
</parameter>
<parameter>
<name>username</name>
<value>mysql</value>
</parameter>
<parameter>
<name>password</name>
<value>mysql</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>30</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>20</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>1000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>30</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
</ResourceParams>

I was running test with 50 threads. Each thread will work with ona and only one record (dedicated min) for the entire test doing select, update. There is 1 second think time between each iteration. What we found is that when the test got started, everything working great with good response time. max_used_connections from myusql show status is about 15-20. After a while, everything becomes slower, the max_used_connections gets to 30 (which is the max setting for connection pool) and eventually, everything stops. I used show engine innodb status\g to get the status (please see below). I also tried to do an insert with mysql client to tn_data_repository table, it hangs. Can someone help us to figure out what the problem is? Really appreciate.

Status:
=====================================
060226 16:15:04 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7435, signal count 6845
Mutex spin waits 193901, rounds 795501, OS waits 2805
RW-shared spins 8385, OS waits 4190; RW-excl spins 10, OS waits 6
------------
TRANSACTIONS
------------
Trx id counter 0 16906521
Purge done for trx's n:o < 0 12175647 undo n:o < 0 0
History list length 17
Total number of lock structs in row lock hash table 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 19752, OS thread id 708950960
MySQL thread id 168, query id 31856712 localhost root
show ENGINE INNODB STATUS
---TRANSACTION 0 0, not started, process no 19752, OS thread id 764873648 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 166, query id 31856709 localhost rts_user update
insert into tn_data_repository values (2000, 'test', now())
---TRANSACTION 0 0, not started, process no 19752, OS thread id 710130608 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 163, query id 31856693 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1018'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 711310256 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 162, query id 31856683 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1030'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 712489904 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 161, query id 31856673 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1003'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 713669552 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 160, query id 31856663 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1005'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 714849200 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 159, query id 31856653 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1013'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 716028848 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 158, query id 31856643 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1035'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 717208496 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 157, query id 31856633 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1025'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 753077168 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 156, query id 31856623 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1045'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8448944 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 155, query id 31856613 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1044'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 9190320 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 154, query id 31856603 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1011'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 11508656 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 153, query id 31856593 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1015'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10316720 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 152, query id 31856587 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1027'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 749538224 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 151, query id 31856577 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1032'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8575920 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 150, query id 31856563 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1021'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 747178928 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 149, query id 31856557 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1028'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 8936368 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 148, query id 31856547 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1047'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 757795760 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 146, query id 31856533 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1031'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 12270512 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 147, query id 31856532 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1041'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 769592240 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 145, query id 31856513 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1039'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 763694000 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 144, query id 31856503 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1024'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 761334704 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 143, query id 31856473 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1037'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10697648 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 142, query id 31856463 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1010'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 10570672 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 141, query id 31856453 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1048'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 11930544 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 140, query id 31856443 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1044'
---TRANSACTION 0 0, not started, process no 19752, OS thread id 5868464 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 139, query id 31856433 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1023'
---TRANSACTION 0 16528038, not started, process no 19752, OS thread id 9919408 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 138, query id 31856417 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1029'
---TRANSACTION 0 16528032, not started, process no 19752, OS thread id 9718704 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 137, query id 31856407 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1033'
---TRANSACTION 0 16528786, not started, process no 19752, OS thread id 8199088 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 135, query id 31856387 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1012'
---TRANSACTION 0 16906437, not started, process no 19752, OS thread id 7945136 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 134, query id 31856359 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1035'
---TRANSACTION 0 16528405, not started, process no 19752, OS thread id 762514352 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 128, query id 31856399 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1026'
---TRANSACTION 0 16528962, not started, process no 19752, OS thread id 9444272 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 127, query id 31856379 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1001'
---TRANSACTION 0 16906457, not started, process no 19752, OS thread id 5995440 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 125, query id 31856351 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1002'
---TRANSACTION 0 16530155, not started, process no 19752, OS thread id 7674800 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 124, query id 31856377 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1041'
---TRANSACTION 0 16527875, not started, process no 19752, OS thread id 8072112 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 122, query id 31856419 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1024'
---TRANSACTION 0 16527832, not started, process no 19752, OS thread id 11635632 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 121, query id 31856411 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1007'
---TRANSACTION 0 16527874, not started, process no 19752, OS thread id 758975408 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 119, query id 31856423 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1003'
---TRANSACTION 0 16528040, not started, process no 19752, OS thread id 11381680 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 118, query id 31856404 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1005'
---TRANSACTION 0 16528588, not started, process no 19752, OS thread id 756616112 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 117, query id 31856392 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1034'
---TRANSACTION 0 16528428, not started, process no 19752, OS thread id 754256816 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 116, query id 31856395 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1038'
---TRANSACTION 0 16904322, not started, process no 19752, OS thread id 9317296 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 114, query id 31856367 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1006'
---TRANSACTION 0 16906441, not started, process no 19752, OS thread id 10824624 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 111, query id 31856363 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1009'
---TRANSACTION 0 16528766, not started, process no 19752, OS thread id 10046384 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 110, query id 31856383 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1000'
---TRANSACTION 0 16530156, not started, process no 19752, OS thread id 9063344 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 109, query id 31856371 eng46.telenav.com 192.168.100.146 rts_user Updating
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1045'
---TRANSACTION 0 15688502, not started, process no 19752, OS thread id 10173360 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 88, query id 31856491 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1042'
---TRANSACTION 0 15689463, not started, process no 19752, OS thread id 134388656 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 87, query id 31856477 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1048'
---TRANSACTION 0 16427756, not started, process no 19752, OS thread id 768412592 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 84, query id 31856355 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1040'
---TRANSACTION 0 15688498, not started, process no 19752, OS thread id 751897520 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 81, query id 31856490 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1049'
---TRANSACTION 0 16906510, not started, process no 19752, OS thread id 8793008
mysql tables in use 1, locked 1
MySQL thread id 80, query id 31856310 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1012'
---TRANSACTION 0 15688500, not started, process no 19752, OS thread id 755436464 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 77, query id 31856493 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1028'
---TRANSACTION 0 15688501, not started, process no 19752, OS thread id 750717872 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 74, query id 31856492 eng56.telenav.com 192.168.101.139 rts_user Updating
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1023'
---TRANSACTION 0 16906520, ACTIVE 157674 sec, process no 19752, OS thread id 10443696, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 83, query id 31856347 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1036'
---TRANSACTION 0 16906519, ACTIVE 157674 sec, process no 19752, OS thread id 749411248, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 75, query id 31856346 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1000'
---TRANSACTION 0 16906518, ACTIVE 157674 sec, process no 19752, OS thread id 10951600, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 79, query id 31856345 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1032'
---TRANSACTION 0 16906517, ACTIVE 157674 sec, process no 19752, OS thread id 11803568, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 126, query id 31856335 eng46.telenav.com 192.168.100.146 rts_user end
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1049'
---TRANSACTION 0 16906516, ACTIVE 157674 sec, process no 19752, OS thread id 5741488, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 108, query id 31856331 eng46.telenav.com 192.168.100.146 rts_user end
update tn_destination_obj set destination = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1039'
---TRANSACTION 0 16906515, ACTIVE 157674 sec, process no 19752, OS thread id 767232944, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 82, query id 31856328 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1004'
---TRANSACTION 0 16906514, ACTIVE 157674 sec, process no 19752, OS thread id 11254704, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 85, query id 31856326 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1034'
---TRANSACTION 0 16906513, ACTIVE 157674 sec, process no 19752, OS thread id 766053296, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 76, query id 31856314 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1026'
---TRANSACTION 0 16906512, ACTIVE 157674 sec, process no 19752, OS thread id 760155056, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 86, query id 31856312 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1001'
---TRANSACTION 0 16906511, ACTIVE 157674 sec, process no 19752, OS thread id 12397488, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320
MySQL thread id 78, query id 31856311 eng56.telenav.com 192.168.101.139 rts_user end
update tn_data_repository set object_data = '??\0t\0@testing...ABCDEFGHIGKLMNOPQRSTUVWXYZ--ABCDEFGHIGKLMNOPQRSTUVWXYZ' where min = '1010'
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
56 OS file reads, 31114 OS file writes, 31088 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 7470761, used cells 175, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 3001026743
Log flushed up to 0 3001026743
Last checkpoint at 0 3001026743
0 pending log writes, 0 pending chkp writes
22707 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2063312448; in additional pool allocated 5810176
Buffer pool size 115200
Free buffers 115112
Database pages 87
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 86, created 1, written 4221
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
10 queries inside InnoDB, 50 queries in queue
1 read views open inside InnoDB
Main thread process no. 19752, id 1078094768, state: waiting for server activity
Number of rows inserted 44, updated 0, deleted 0, read 4730251
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Options: ReplyQuote


Subject
Views
Written By
Posted
innodb update locks table...
6154
February 26, 2006 04:23PM
3369
February 27, 2006 05:45AM
2754
February 27, 2006 01:58PM
2886
February 28, 2006 04:41PM
2667
February 28, 2006 05:57PM
2587
February 28, 2006 06:28PM
2466
March 01, 2006 08:26AM
5182
March 10, 2006 04:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.