Our custom written sequences for mysql have issues around transactions which are rolled back.

Right now the implementation is to take a batch of 1000 sequences from the HQ_SEQUENCE table (innodb engine) and populate the HQ_MEM_SEQUENCE table (memory engine) to avoid the nextseqval() call hanging (and timing out) when it is in a transaction.

When it is time for the batch update to occur if the outer transaction is rolled back the HQ_SEQUENCE table will not reflect the last sequence number correctly in HQ_MEM_SEQUENCE, since innodb engine is transactional and the HQ_SEQUENCE table will be rolled back.

Right now I see two ways of solving this:

1) everytime nextseqval() is called the SEQUENCE tables are checked to make sure that the seq_val is correct based on max(seq_val) from HQ_MEM_SEQUENCE. The problem here is that it is very expensive to do and HQ is much slower during startup and other areas of the app.

2) only execute the checking from # 1 when count(seq_val) == 1 in HQ_MEM_SEQUENCE. This way is a lot faster, but has another issue. If a rollback occurs right before the server is shutdown then we are back to square one. The solution is then to increment the HQ_SEQUENCE table by the <batchsize> everytime HQ is started up. To do this it will take many more sequences, so HQ_SEQUENCE should be changed to an UNSIGNED INT or even UNSIGNED LONG (although I think that is an over-kill).