5

I now manage a MySQL database that was designed and created by another.

I am confused about a certain setup that the original developer created..

He did not use auto increments for table IDs. Instead, he created a table called system_sequences which has two columns:

system_sequences.Table_Name 
system_sequences.Next_Value

Here is an example of a table and how it currently inserts into the table (try my best):

there is a table called customer with an unique ID column.

If I want to insert into customer, currently I have to do the following:

SELECT system_sequences.Next_Value 
FROM system_sequences 
WHERE system_sequences.Table_Name = 'customer'

save the next_value into a variable and use it as the next ID for customer

then I have to:

UPDATE system_sequences 
SET Next_Value = Next_Value + 1 
WHERE system_sequences.Table_Name = 'customer'

I did not design this, I do not like it in any way shape or form ( I actually hate it ) and I want to convert everything carefully to auto_increment

But is there a reason for this or a benefit that I am not aware of? I have never seen anything like it before. And there is no documentation for this database that explains anything.

4
  • 1
    How do you stop two concurrent transactions both reading the same next value? Commented Mar 16, 2012 at 15:50
  • @MartinSmith I have no idea ..
    – Jeff
    Commented Mar 16, 2012 at 15:50
  • 1
    In MySQL I really have no idea. In MS SQL Server, one could specify exclusive row locks to be held to the end of the transaction on a table hint on the above SELECT query and no one will be able to read that record until the txn ends. Commented Mar 26, 2012 at 17:39
  • @FabricioAraujo: I'd expect to need a transaction wrapping all the statements that select the next value, insert into the target table, and update the next value. Docs for serializable transactions You can also lock tables in MySQL. Commented Jul 31, 2012 at 1:12

2 Answers 2

1

The original designer probably had some kind over quick overview of DB resources in mind.

I know Oracle supports a database object called a SEQUENCE that that by just summoning the next value, the auto_increment is performed with the sequence object.

You create it like this:

CREATE SEQUENCE eseq INCREMENT BY 1;

The first reference to eseq.nextval returns 1.

Again, just call eseq.nextval in your trigger and you are assured a safe number to use each time. MySQL has nothing like this. The original designer may have had this in mind and simply emulated it in light of MySQL not having database objects like an Oracle Sequence.

I can also assume that there is no mechanism or infrastructure for collecting and reusing ids that have been deleted, thus leaving gaps in ids.

Looking at the system resource spec you mentioned, you could guess at how many rows were in a table instead of running SELECT COUNT(*) FROM customers;.

If I had to guess why this was done, my guess would be that the designer did not know about or trust in MySQL having an INFORMATION_SCHEMA database. MySQL has had INFORMATION_SCHEMA database since the inception of MySQL 5.0. All versions prior to MySQL 5.0 do not have an INFORMATION_SCHEMA database.

4

I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for accounting auditing.

2
  • Yep, IDENTITY columns in SQL Server aren't guaranteed to generate gapless sequences. I bet the same goes for MySQL's auto_increment. Commented Mar 16, 2012 at 15:25
  • I think that transactions even if rolled back still increment the id by 1, but we don't have anything that would need to be that strict in the database. Maybe he was moving in that direction though for other systems that where planned.
    – Jeff
    Commented Mar 16, 2012 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.