Query Locking Problem

[Neil Edgar]

I have a MyISAM table that is accessed by two java clients (via JDBC). One client is peforming frequent INSERT operations (~1 per sec) and the other process is performing lengthy SELECT operations (>>1 second). The table has about 2.5 million records. On host #1, the INSERT operations occur at the same time as the SELECT operation, however on host #2 the INSERT operations are shown with a "Locked" state until the SELECT operation completes. I cannot identify cause of this difference in behaviour. The hosts are running the same versions of: Operating System, MySql, JDBC and Java. The "SHOW VARIABLES" are identical with the exception of timezone and character_sets. Host #1 is a Sun Ultra-5_10. Host #2 is a Sun-Fire V240.

What is the expected behaviour for MyISAM "READ-COMMITTED"?

mysql> desc Events;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| equipmentName | varchar(255) binary | | MUL | | |
| sequenceNo | varchar(30) binary | | | | |
| equipmentOID | varchar(255) binary | | | | |
| cellId | int(11) | | MUL | 0 | |
| eventCause | int(11) | | | 0 | |
| eventSeverity | int(11) | | | 0 | |
| dateTime | varchar(30) binary | | | | |
| eventType | int(11) | | | 0 | |
| emDateTime | varchar(30) binary | | MUL | | |
+---------------+---------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

Host #1
mysql> show processlist;
+------+--------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 1 | server | localhost | ipw | Sleep | 1549 | | NULL |
| 2 | server | localhost | ipw | Sleep | 549 | | NULL |
| 1772 | root | localhost | ipw | Query | 0 | NULL | show processlist |
| 1877 | client | 10.0.0.17 | ipw | Query | 40 | Sending data | SELECT count(*) as noRows, max(emDateTime) as maxRecvTime, min(emDateTime) as minRecvTime FROM Even |
+------+--------+-----------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+



Host #2
mysql> show processlist;
+---------+--------+---------------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+--------+---------------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+
| 3 | server | localhost | ipw | Sleep | 740 | | NULL |
| 4 | server | localhost | ipw | Sleep | 1032 | | NULL |
| 2382509 | root | localhost | ipw | Query | 0 | NULL | show processlist |
| 2383360 | client | 10.231.253.26 | ipw | Query | 27 | Sending data | SELECT count(*) as noRows, max(emDateTime) as maxRecvTime, min(emDateTime) as minRecvTime FROM Even |
| 2383361 | server | localhost | ipw | Query | 26 | Locked | INSERT INTO Events VALUES("INC_North_Melksham_D_58318","37846",".1.3.6.1.4.1.5586.3.2.2.2.20.1.1.6", |
+---------+--------+---------------+--------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+