Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes MySql - simulating sequences using increment and last_insert_id Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql - simulating sequences using increment and last_insert_id " Watch "MySql - simulating sequences using increment and last_insert_id " New topic
Author

MySql - simulating sequences using increment and last_insert_id

Bonnie Kenison
Greenhorn

Joined: Jan 31, 2011
Posts: 7
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.

Any ideas?
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
Bonnie Kenison wrote:I am trying to simulate sequences in MySql

Why are you doing that when mysql has AUTO_INCREMENT?





Bonnie Kenison
Greenhorn

Joined: Jan 31, 2011
Posts: 7
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.
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
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.

Locks on reading only occur if you select for update as explained here http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_read-committed but I would advice against such locking as you probably lose a lot of performance.
Did you see the approach used here: http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/ ? MAybe it can give you an idea on how to do what you are trying to do.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1706
    
  14

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..


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySql - simulating sequences using increment and last_insert_id