[resending this with another mail user agent to bypass the spam filter]
Dear Andy,
I have this weird problem with 1 transaction locking out another for no
apparent reason and in an inconsistent fashion. I'm using mysql 4.0.18
with innodb tables. The table in question has 2 primary keys: CID and
day. The table has existing data, 1 row per CID (from 1 to 150), all
with day 2004-06-07. I have 2 processes running, 1 doing an update
transaction, the other doing an insert transaction.
The first transaction performs an update first:
UPDATE test SET data0=data0+1 WHERE CID = '97' AND day = '2004-06- 07';
The 2nd transaction performs an insert after the 1st transaction's
update, but before it commits:
INSERT INTO test (CID, day, data0) VALUES ('96', '2004-06-08', '1');
The insert waits forever (if the 1st transaction has not commit or rollback yet), and eventually I get a lock wait timeout.
The lock wait timeout is probably caused by a gap lock set by the update statement. If InnoDB is able to determine that the statement affects exactly one record, it will lock that single record. Otherwise, it will acquire a gap lock that prevents the insertion of records whose primary key value would be immediately before that record.
For instance, if the predecessor of the record being updated
('97','2004-06-07') is ('94','2004-06-07'), you won't be able to insert
records with keys ('95','2004-06-07') or ('96','2004-06-07') until the
gap lock is released, that is, the update transaction has been committed or rolled back.
If you posted the SHOW INNODB STATUS and SHOW CREATE TABLE output, we could verify this analysis.
With best regards,
Marko Mäkelä
Senior Software Engineer
Innobase OyPS: when replying, replace markom with marko.makela in my e-mail address.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
