Replies to post #73604 on The Question and Answer Board (IHUB)

[IH Geek [Dave]]

The identity value is issued on the insert statement and is either used on a commit or is discarded. Hence the breaks in the identity sequence. I doubt the collision is happening due to a duplicate message ID.

Users winding up in unexpected places is probably caused by not detecting and changing the action when failed transactions occur. The inconsistent data probably ties back to the various states of isolation levels.

Until the insertion transaction is committed, the data is uncommitted. I can see a skip/duplicate in the intra-board sequences happening like this:

Two posts occur on a board at the same time. Part of the tran is to determine the next msg_number on that board. Post #1 gets the next msg_number and executes the insert statement. It is uncommitted at this point.

Transaction #2 does its insert statement.

If the isolation level of that transaction is read uncommitted then it would would know of the uncommitted sequence from trx # 1 and add 1 to that. If transaction #1 subsequently fails there would be a skip in the intra-board sequence.

If the isolation is set to read committed, transaction #2 would not see the msg_number used by the uncommitted trx #1, and uses the same msg_number. A duplicate sequence occurs.

Then we have to balance the default isolation level for the database against any overrides set at the connection level, and lock hints used on the select statements.

Where's the Advil....