MySQL Forums
Forum List  »  InnoDB

Re: Transaction deadlock with many threads
Posted by: Alex Matrosoff
Date: April 26, 2010 02:35PM

>* 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).


Possibly yes.
Because there is many concurrent locked queries. Maybe they are doing prequery or somewhat and caching the changing row.

>* If there is no useful index, then all the SELECTs will be table scans.
there is

>* How many rows?
5k-100K

>* 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.

all tx designed to consume as less code and time as it possible. Tx runs immediately in singlethread mode.

>* 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. Kinda while (1) { fork ();}
However, i expect proper query serialization from innodb; or table locking from mysql. no matter how many threads knocking same 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:


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


maybe the cause of the problem in poor db design?
the idea is : i have a base with urls. I have a worker, which archives content from those urls. Worker run in 1K threads.
I made a pool of urls for workers.
worker start and saying : give me an ANY url which is not in use (where status='FREE')
then - mark this url as in use (status="BUSY")

i believe the best way is "select for update; update"

possibly this is wrong and i have to fall back to the 2005 technique :
and uuid field to the db : uuid char (32) unique, pk.
$x=random_uuid();
update table set uuid=$x where status='FREE' limit 1;
select * from table where uuid=$x

There is no locks and queries should be properly serialized.

What is your opinion?



Edited 1 time(s). Last edit at 04/26/2010 02:39PM by Alex Matrosoff.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Transaction deadlock with many threads
1438
April 26, 2010 02:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.