2

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.

Improve this question
15
  • 1
    For mysql, you might want to explore using LAST_INSERT_ID().
    – user40980
    Feb 28, 2013 at 18:12
  • Ah, I should have been clearer with my tagging and my question. We're using Hibernate, and we're avoiding using native queries. I'll fix the question to reflect that.
    – Apropos
    Feb 28, 2013 at 18:14
  • 1
    Not sure if this will work but: A trigger on your main table that when you insert, the triggers inserts the value of LAST_INSERT_ID() to an axilliary table (named LAST_INSERTED_ID, with one column whose name you can probably guess) and then use Hibernate to read that table. It's ugly but it might work. I guess you can't use stored procedures, can you? Have you checked to see if Hibernate has some built-in mechanism to get LAST_INSERT_ID()?
    – FrustratedWithFormsDesigner
    Feb 28, 2013 at 18:17
  • 1
    Also, why is this tagged with Oracle and MySQL? Which one are you using? Oracle's Sequences are different from MySQL's autoincrement columns.
    – FrustratedWithFormsDesigner
    Feb 28, 2013 at 18:19
  • 1
    @Apropos: Oracle does not have auto-incrementing columns. It has Sequences which are objects in their own right and can be queried to see what the last value was, you could also have a view wrap that which might be more Hibernate compatible. MySQL has auto-increment columns, and I haven't worked with them for a while but they are different so the solution you use will probably be database-specific.
    – FrustratedWithFormsDesigner
    Feb 28, 2013 at 18:26

1 Answer 1

Reset to default
3

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.

Improve this answer
3
  • I don't think that can happen in MySQL (since I don't think it reserves ranges like that). But that is certainly an interesting case for when the sequence might not be reliable in an Oracle DB. We don't actually ever care about the time or when the row was inserted except immediately after it is inserted. Given that assumption, if we wrap the insert and select within the same transaction, aren't we guaranteed a good result at that point? Obviously once that transaction ends, the issue you've pointed out exists.
    – Apropos
    Feb 28, 2013 at 20:53
  • @Apropos: I think in Oracle a sequence's cached block of numbers is available to anything that makes use of the sequence (I don't think different blocks are assigned to different connections) so I'm not sure this would happen in Oracle.
    – FrustratedWithFormsDesigner
    Feb 28, 2013 at 21:08
  • Wait...Hibernate does that for you. When you insert an object and commit the unit of work, the object's id is updated with the db provided value.
    – Michael Brown
    Feb 28, 2013 at 21:34

Your Answer

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

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