Transaction deadlock with many threads
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.
Subject
Views
Written By
Posted
Transaction deadlock with many threads
4132
April 23, 2010 12:15PM
1501
April 24, 2010 01:47PM
1369
April 26, 2010 02:16PM
1439
April 26, 2010 02:35PM
1480
April 26, 2010 08:39PM
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.