Re: Transaction deadlock with many threads
the problem is - when they start, system going to crockscrew. First deadlock leads to the bunch of consequential deadlocks.
>* It is probably unrealistic to do the LIMIT 1 without an ORDER BY -- you could be getting a random row (but probably are getting the same row).
maybe yes. they're knocking the same row. But there is select for update, so they should use next row? or wait while row is commited?
Probably problem is :
first select locks row (status=0, so it ok for the "where clause").
second select - seeing same row but waiting till first select commits update. the same row = status still eq 0. second select caching pointer to the row or somewhat.
first select changing status to 1, and commits tx.
after that second select wake up, it tries to use that row because it was in the where clause. But it changed while it was sleeping. So it became crazy?
>* If there is no useful index, then all the SELECTs will be table scans.
there is idx.
> * How many rows?
5000
>* If you ran one copy of this 'transaction', how long would it take? I am (indirectly) asking if you have a sleep() or lengthy operation inside the lock/begin.
immediately. all txes made in the way to conserve as less code and time as it possible.
>* Are you trying to launch all the threads at exactly the same time? (I don't mean in the same second, I mean in the same millisecond.) If so, this is "unrealistic".
Yes I do. like while (1) { fork ();}
However, i believe that innodb serialize such queries in the right way.
CREATE TABLE `grabber_in` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varchar(1024) NOT NULL,
`status` enum('BUSY','DONE','FREE') NOT NULL DEFAULT 'FREE',
`updated` datetime NOT NULL,
`clean_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3897252 DEFAULT CHARSET=utf8
*************************** 9. row ***************************
Name: grabber_in
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9853
Avg_row_length: 427
Data_length: 4210688
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: 3922801
Create_time: 2010-04-19 15:38:52
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: