MySQL Forums :: Newbie :: Simulating sequences


Advanced Search

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


Subject Written By Posted
Simulating sequences Bonnie Kenison 06/30/2014 10:26AM
Re: Simulating sequences Peter Brawley 06/30/2014 10:49AM
Re: Simulating sequences Bonnie Kenison 07/02/2014 01:09PM
Re: Simulating sequences Peter Brawley 07/02/2014 01:37PM
Re: Simulating sequences Bonnie Kenison 07/02/2014 01:47PM
Re: Simulating sequences Peter Brawley 07/03/2014 12:17PM
Re: Simulating sequences Rick James 07/07/2014 08:50AM
Re: Simulating sequences Peter Brawley 07/07/2014 11:25AM
Re: Simulating sequences Hiring Simulation 07/07/2014 12:29PM
Re: Simulating sequences Peter Brawley 07/07/2014 08:16PM
Re: Simulating sequences Bonnie Kenison 07/08/2014 02:23PM
Re: Simulating sequences Rick James 07/08/2014 03:08PM
Re: Simulating sequences Bonnie Kenison 07/09/2014 10:12AM
Re: Simulating sequences Bonnie Kenison 07/09/2014 04:57PM
Re: Simulating sequences Peter Brawley 07/08/2014 08:03PM
Re: Simulating sequences Bonnie Kenison 07/09/2014 10:17AM
Re: Simulating sequences Peter Brawley 07/09/2014 12:56PM
Re: Simulating sequences Rick James 07/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.