MySQL Forums
Forum List  »  InnoDB

Transaction deadlock with many threads
Posted by: Alex Matrosoff
Date: April 23, 2010 12:15PM

We have tablex.
id : int, primary key, autoincrement
url : char[256]
status : int

MyISAM scenario:

lock tables tablex write
select * from tablex where status=0 limit 1
update tablex set status=1 where id=$id
unlock tables

InnoDB scenario:

begin
select * from tablex where status=0 limit 1 for update
update tablex set status=1 where id=$id
commit

In MyIsam case starting from 200 concurrent threads locks began to hang. They hangs for a 10 minutes or so on.

In InniDB scenario we are end up with this error: "1213 Deadlock found when trying to get lock; try restarting transaction" for EACH consequent request. You see there can't be any deadlocks. But the are!

Checked across 5.0, 5.1 versions. On i32 and i64 systems. Server have enough capacity to handle 200 concurrent queries: 4x Core Quad with 32 G ram.

Here follows my tuning.conf

[mysqld]
skip-external-locking
innodb_file_per_table
innodb_buffer_pool_size = 1G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency = 32
innodb_lock_wait_timeout=300
transaction-isolation=REPEATABLE-READ
skip-name-resolve
#slow_query_log
max_connections=1001
max_user_connections=1000
table_cache=32k
query_cache_limit=1G
key_buffer_size=4G



Edited 1 time(s). Last edit at 04/23/2010 12:12PM by Alex Matrosoff.

Options: ReplyQuote


Subject
Views
Written By
Posted
Transaction deadlock with many threads
3466
April 23, 2010 12:15PM


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.