Simulating sequences
I am trying to simulate sequences in MySql. I have read on SO and elsewhere several suggestions on how to do this. I believe I am doing it correctly. I have an update statement that increments a column in my sequence table that looks like this:
query = "UPDATE sequence SET id = LAST_INSERT_ID(id + 1) WHERE sequence_id = 'MySequenceName'";
int rowsUpdated = statement.executeUpdate(query);
Then after a successful update, I retrieve the sequence value via "SELECT LAST_INSERT_ID()".
This works well until the system is heavily loaded. When heavily loaded, I sometimes get a zero value as the rowsUpdated value. I assume this is because two threads were doing the update at the same time. I figure that each thread is getting the same value for id, incrementing it and then both updating sequence_id with the same value. So, thread 1 would get rowsUpdated to be 1, but since thread 2 is updating with the same value, no change is made and rowsUpdated for thread 2 is 0.
I am running MySql version 5.1. My transaction level on the connection is READ_COMMITTED. The column 'sequence_id' is the primary key on the table. I am using separate connections for each thread. Oddly, I have several installations of this code that are just as heavily loaded, but not experiencing this issue. I am considering upgrading the version of MySql at this site, but I haven't seen any talk about that being the issue.
Is it incorrect to think that the update statement should be locking the table row PRIOR to reading id, incrementing it and doing the update?
Subject
Written By
Posted
Simulating sequences
June 30, 2014 10:26AM
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.