Is it a bad practice to rely on an auto-incrementing primary key to sort rows in a table? A coworker and I were having an argument about this subject. We need to be able to find the last-inserted row in a table, and we have no create/update time columns (which might be considered a bad practice in itself, but that’s a different argument). We obviously can’t rely on the natural order of the rows, since that can be inconsistent. Is there anything inherently wrong with using the primary key in this way, given that it’s an immutable value?
I should have noted: our implementation is using Hibernate (an ORM) to fetch our objects. We’re not using any native queries — only JPQL or Hibernate’s functionality.
15
If you care about time, time should be in your database. Period. Want to see a case where using a sequence could fail (I’m not sure if the same might happen with MySQL autoincrements)?
When you request a value from a sequence inside of a transaction, you don’t just pull the next value, the DB reserves a whole block of values for you. This is done to reduce the overhead of properly locking the sequence and handling multiple simultaneous connections. Let’s look at this sequence of events:
- Client #1 connects to server
- Client #1 requests a value from a sequence
- DB reserves 1-10 for Client #1, gives Client #1 the value 1
- Client #1 inserts the 1 into a table
- Client #1 decides to do something that will take some time
- Client #2 connects to server
- Client #2 requests a value from a sequence
- DB reserves 11-20 for Client #2, gives Client #2 the value 11
- Client #2 inserts the 11 into a table
- Client #2 commits & disconnects
- Client #1 requests the next value from the sequence
- DB gives Client #1 the value 2
- Client #1 inserts 2 into a table
- Client #1 commits and disconnects
What’s the state of the database now? You have 1, 2 & 11 in your table but the row with the 2 is the most recent. Sorting by this value gives you the wrong result.
3