MySQL Forums
Forum List  »  Newbie

Simulating sequences
Posted by: Bonnie Kenison
Date: June 30, 2014 10:26AM

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?

Options: ReplyQuote

Written By
Simulating sequences
June 30, 2014 10:26AM
June 30, 2014 10:49AM
July 02, 2014 01:37PM
July 03, 2014 12:17PM
July 07, 2014 08:50AM
July 07, 2014 11:25AM
July 07, 2014 08:16PM
July 08, 2014 03:08PM
July 08, 2014 08:03PM
July 09, 2014 12:56PM
July 09, 2014 03:13PM

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.