Dear Marko,
It does look like the result of a gap lock, but notice that I used both
primary keys in the where clause of my update query, therefore it should
only match (and update) 1 row. From my understanding (correct me if I'm
wrong, my knowledge of how mysql works is gleaned from the mysql docs
and the comments in the source), updates that uses all primary keys does
not place gap locks. FYI, there is already a row in the table with those
primary keys, so the update query did update 1 row (and not 0 rows).
And notice that in both of my test cases, the only difference is in the
primary key values. However, 1 test case did not get a lock wait timeout
(as expected), and the other does. This inconsistency is what stumped
me.
Here's the transaction section of show innodb status:
------------
TRANSACTIONS
------------
Trx id counter 0 4372282
Purge done for trx's n:o < 0 4372279 undo n:o < 0 0
Total number of lock structs in row lock hash table 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15224, OS thread id 36874
MySQL thread id 32, query id 1338396 localhost root
show innodb status
---TRANSACTION 0 4372281, ACTIVE 10 sec, process no 15226, OS thread id
45068 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 31, query id 1338395 localhost root update
INSERT INTO test (CID, day, hour0) VALUES ('96', '2004-06-08', '1')
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 242 n bits 96 table test/test index
PRIMARY trx id 0 4372281 lock_mode X insert intention waitingRecord
lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;
asc supremum.;;
------------------
---TRANSACTION 0 4372279, ACTIVE 48 sec, process no 15225, OS thread id
40971
3 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 27, query id 1338390 localhost root
Here's the create table statement from show create table:
CREATE TABLE `test` (
`CID` int(10) unsigned NOT NULL default '0',
`day` date NOT NULL default '0000-00-00',
`hour0` float(7,6) unsigned NOT NULL default '0.000000',
`hour1` float(7,6) unsigned NOT NULL default '0.000000',
`hour2` float(7,6) unsigned NOT NULL default '0.000000',
`hour3` float(7,6) unsigned NOT NULL default '0.000000',
`hour4` float(7,6) unsigned NOT NULL default '0.000000',
`hour5` float(7,6) unsigned NOT NULL default '0.000000',
`hour6` float(7,6) unsigned NOT NULL default '0.000000',
`hour7` float(7,6) unsigned NOT NULL default '0.000000',
`hour8` float(7,6) unsigned NOT NULL default '0.000000',
`hour9` float(7,6) unsigned NOT NULL default '0.000000',
`hour10` float(7,6) unsigned NOT NULL default '0.000000',
`hour11` float(7,6) unsigned NOT NULL default '0.000000',
`hour12` float(7,6) unsigned NOT NULL default '0.000000',
`hour13` float(7,6) unsigned NOT NULL default '0.000000',
`hour14` float(7,6) unsigned NOT NULL default '0.000000',
`hour15` float(7,6) unsigned NOT NULL default '0.000000',
`hour16` float(7,6) unsigned NOT NULL default '0.000000',
`hour17` float(7,6) unsigned NOT NULL default '0.000000',
`hour18` float(7,6) unsigned NOT NULL default '0.000000',
`hour19` float(7,6) unsigned NOT NULL default '0.000000',
`hour20` float(7,6) unsigned NOT NULL default '0.000000',
`hour21` float(7,6) unsigned NOT NULL default '0.000000',
`hour22` float(7,6) unsigned NOT NULL default '0.000000',
`hour23` float(7,6) unsigned NOT NULL default '0.000000',
PRIMARY KEY (`CID`,`day`)
) TYPE=InnoDB
Regards,
Andrew
On Mon, 2004-06-14 at 07:34, Marko Mäkelä wrote:
> 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 Oy
>
> PS: 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]
