auto_increment key rolled back after deadlock
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| Percona Server moved to https://jira.percona.com/projects/PS |
New
|
Undecided
|
Unassigned | ||
Bug Description
I have a system where we bulk load data into a table with insert into table_a select * from tmp_table where xxx order by yyy
The inserts seem to be hitting a typical deadlock case where we have a primary index with two columns an auto_increment id and slowly moving constant. The table also has a second unique index that is the true unique key for the data that includes a 16 BIT hash. The hash basically makes this index very random. We order by the secondary index on insert (see above).
From time to time we get deadlocks on these two inserts. That I somewhat understand. What is also happening is that after the insert is rolled back. The auto_increment next value is now behind the current max(id) for the auto_increment column in the table.
Based on my reading of the docs etc, this should never happen.
I am attaching a text document that I wrote up with a lot more detail...
| JeffWRule (jeffwrule) wrote | #1 |
| Shahriyar Rzayev (rzayev-sehriyar) wrote | #2 |
Percona now uses JIRA for bug reports so this bug report is migrated to: https:/