33 reputation
5
bio website vitalidze.github.com
location Moscow, Russia
age 27
visits member for 1 year, 4 months
seen Dec 9 '13 at 10:37

Jan
25
awarded  Popular Question
Jan
5
awarded  Scholar
Jan
5
accepted MySQL InnoDB locks primary key on delete even in READ COMMITTED
Dec
18
awarded  Commentator
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
I'll give it a try today or tomorrow. Then report results here.
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
1) in a real cases day_position contains ~300-400K rows, ivehicle_days 50-100K rows. 2) On sample data it runs fast, less than 1 second. 3) About stored procedures - we're not using it now. Thanks for suggestion. Finally, we've ended in a two-stage (or double query) cleaning scheme. First query checks whether there are any of rows matching the WHERE clause with SELECT statement (it doesn't lock rows). Next one is the actual DELETE if check succeeds. This helps.
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
I've tried both USE INDEX and FORCE INDEX, no help.
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
As I've said somewhere here there are several DELETE queries (~10) that execute in parallel transactions. DELETE from proc_warnings table is just one of them. All indices are properly defined, MySQL uses them when either proc_warnings is big enough or joined tables are not so big. I believe that this particular query works pretty fast. However, it blocks entire proc_warnings and all other concurrent transactions have to wait until all (~10) DELETEs are finished. They may took long depending on the size of tables.
Dec
17
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
The WHERE condition is evaluated when query completes. Isn't it? I thought that lock is released right after some of concurrent queries executes. That's the natural behavior. However, this doesn't happen. Neither of suggested queries in this thread helps to avoid the clustered index locking in the proc_warnings table. I think I'll file a bug to the MySQL. Thanks for your help.
Dec
17
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
Unfortunately this doesn't help, i.e. rows in proc_warnings still get locked. Thanks anyway.
Dec
13
awarded  Supporter
Dec
13
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
About rolling back single statement - I understand this as if single statement fails within single transaction it may still hold the locks. That's ok. My big question is why it doesn't release non-matching row locks after successfully processing DELETE statement.
Dec
13
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
I've read about the things you describe in MySQL docs. But as for me the main feature of READ COMMITTED is how it deals with locks. It should release the index locks of non-matching rows, but it doesn't.
Dec
13
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
That's not true. Why the SET AUTOCOMMIT=0 should reset the isolation level for next transaction? I believe it starts a new transaction if none was started before (which is my case). So, to be more precise the next START TRANSACTION or BEGIN statement is not necessary. My purpose of disabling autocommit is to leave transaction opened after the DELETE statement execution.
Dec
13
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
Of course. The problem is that all other transactions must wait until one of them commit changes. The simple test case doesn't contain a commit statement to show how to reproduce the issue. If you run commit or rollback in non-waiting transaction it releases lock simultaneously and waiting transaction completes it's work.
Dec
13
awarded  Student
Dec
13
asked MySQL InnoDB locks primary key on delete even in READ COMMITTED