I am trying to simulate sequences in MySql. 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. I can't figure out why the update statement isn't locking the table row PRIOR to reading id, incrementing it and doing the update.
Bonnie Kenison wrote:I am trying to simulate sequences in MySql
Why are you doing that when mysql has AUTO_INCREMENT?
Joined: Jan 31, 2011
As I stated, we are trying to simulate the use of sequences. I am not going to get into the reasons why we need sequences instead of auto-increment fields. Just suffice it to say that we want to use a sequence model. Oracle and SqlServer offer sequence objects, MySql currently does not. We are trying to get our code to work with multiple databases.
Joined: Mar 17, 2012
Bonnie Kenison wrote:As I stated, we are trying to simulate the use of sequences. I am not going to get into the reasons why we need sequences instead of auto-increment fields. Just suffice it to say that we want to use a sequence model. Oracle and SqlServer offer sequence objects, MySql currently does not. We are trying to get our code to work with multiple databases.
It's important to post the reasons because that determines what (better) alternatives exist.
Are you sure this is the right approach? You are writing DB-specific code to bodge together an implementation of something that doesn't exist in MySQL, all in the name of having database-agnostic code. Why not step back and write code that uses the features of each specific database properly, then wrap that code in your database-agnostic API (or use an existing API like JPA/Hibernate) if necessary? Then if you deploy your system to a different database, your platform-specific changes are still aimed at exploiting the features of that platform instead of working against them.
Writing a robust, transaction-safe, multi-user, scalable unique sequence generator takes quite a lot of work to get right. You'd be better off devoting those rsources to a more maintainable and flexible approach using the features your chosen database already provides. In the long run you'll spend more time fixing problems in your home-made sequence generator than you would spend maintaining clean code that uses the appropriate generator for each database.
Incidentally, home-made sequences may be a real performance bottle-neck if you have a lot of transactions fetching values off the "sequence", as each fetch needs to ensure a unique value. For example, Oracle sequences provide support for caching to help get around this problem - you may need to come up with your own solution to ensure fast access to unique sequence values from multiple transactions..