1

Some table has an INT column with primary key and identity constraints. When two concurrent transactions are inserting a row into the table, which of the transactions will end up with HIGHER column value, the one that executed INSERT statement later or the one that ended/committed later?

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
ivan
  • 628
  • 5
  • 14
  • 1
    The `identity` value is assigned at the moment the row is persisted to disk - so when the transaction starts really is irrelevant - the moment when data gets written is when the identity values are handed out – marc_s Sep 20 '16 at 05:14
  • 1
    The identity will be considered used even if the transaction rolls back, so the lower id value would be for the insert statement that started first. The fact that the column is the primary key is irrelevant to the question. – Zohar Peled Sep 20 '16 at 05:16
  • ok, I re-phrased the question. I need to understand whether it is possible that a transaction that started earlier but ended later can generate LOWER column value. – ivan Sep 20 '16 at 06:01
  • 1
    @ivan - if you need some form of temporal ordering, do something to specifically obtain that, explicitly. Don't assume *anything* about the value of identity columns. You should treat them as opaque blobs that *happen* to fit in numeric columns. Don't try to perform any reasoning about their *values*. – Damien_The_Unbeliever Sep 20 '16 at 06:13
  • @Damien_The_Unbeliever, good point. I just need to understand where some bug could come from. – ivan Sep 20 '16 at 06:16
  • Also, consider questions such as [this one](http://stackoverflow.com/q/282451/15498) (there are plenty to be found if you search around the terms `identity rollback`). Identity values are consumed even when a transaction is rolled back. Therefore, logically, it cannot possibly be related to when a transaction is committed. – Damien_The_Unbeliever Sep 20 '16 at 06:29

1 Answers1

0

ID is being generated during execution of INSERT operation, so the insert (1) that was executed before insert (2) will have lower ID (of course, if the increment is positive :) )

Anton
  • 2,846
  • 1
  • 10
  • 15
  • does that depend on when a transaction is committed? or is identity value claimed directly when INSERT is processed within a transaction flow? – ivan Sep 20 '16 at 06:03
  • It does not matter whether transaction is committed or rolled back. Identity value is claimed when INSERT is processed. – Anton Sep 20 '16 at 23:01