Hi all,
This is my first time on the mysql mailing list, I appreciate if someone
can help me with a major problem I encountered with mysql.
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.
However, if I do the same procedure with different CIDs, I do not get a
lock wait timeout:
If 1st transaction do:
UPDATE test SET data0=data0+1 WHERE CID = '95' AND day = '2004-06-07';
and 2nd transaction do:
INSERT INTO test (CID, day, data0) VALUES ('94', '2004-06-08', '1');
The setup are exactly the same with both tests, but 1 succeeds while the
other fails. The test also works fine with other CID combinations I
tested; it only fails with a lock wait timeout for 97 & 96.
Looking at show innodb status, it shows the update transaction in the
1st test holds 3 lock structs, while the update transaction in the 2nd
test holds only 2 lock structs. Why would they hold a different number
of locks? From my understanding of mysql locking, update queries that
uses all primary keys only locks the row that is updated. So shouldn't
the number of locks be 1? And shouldn't the number of lock structs be
consistent in all the tests?
What's worse is that I cannot consistently reproduce this problem; it
seems to depend on the dataset I'm using. It does happen often enough to
cause major problems with our application. I really appreciate with
someone can enlighten me on how mysql locking works with regards to
update queries (like what kind of locks does mysql place exactly, and
whether there are any exceptions that cause the inconsistency I'm
seeing).
Regards,
Andrew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
