0

I have two threads that update table A at the same time. Each thread updates its own range of rows (they don't have intersections). Each thread works in READ UNCOMMITTED transaction. There is an AFTER UPDATE trigger on table A which adds records to the table B. Table B has BIGINT primary key. In which order the records on the table B will be added? Is it possible that the records generated by the first transaction will be mixed with records generated by the second transaction or it's always one transaction writes it's data and the second writes only after that?

I mean is it possible that when the first transaction is completed I'll have rows in table B with PK values 3,6,8,9 and when the second transaction is completed there will be added PK values 4,5,7,10? Or the range of PK values generated by the first transaction won't be mixed with values generated by the second one? Transaction that reads from table B is READ COMMITTED

Random
  • 3,807
  • 2
  • 30
  • 49
  • If these are autonumber columns (a.k.a. `IDENTITY`) then I'd say making *any* assumptions about their values is a mistake. They should be treated as opaque blobs that happen to fit into the same number of bits as a numeric column, but nothing should process their *values*. – Damien_The_Unbeliever Sep 22 '15 at 12:52
  • Why is it a mistake? The MSDN documentation describes that IDENTITY ( seed , increment ) provides `seed` initial value and `increment` step. And I use IDENTITY(1,1) so that I expect that the difference between two sequential values is 1 – Random Sep 22 '15 at 13:12
  • that right there in your comment is a common example. There can be multiple reasons why identity values might skip a value (or even a few thousand values) - search here on SO for plenty of questions about that - and trying to "fix" that issue (so that there are no gaps) is expensive. If you don't *need* sequential values with no gaps, then `IDENTITY` is a fine solution. – Damien_The_Unbeliever Sep 22 '15 at 13:17
  • Please tell me you are not doing updates with read uncommitted. You can and will end up with index corruption doing that. – Sean Lange Sep 22 '15 at 13:23
  • It's ok that identity values have gaps. But I need that each next value was greater than the previous one. So I'm wondering is it possible that 2 transactions generate identities at the same time or first transaction will lock the table until it's committed or rolled back? – Random Sep 22 '15 at 13:24
  • Yes, I'm doing updates with read uncommitted. How can it lead to index corruption? – Random Sep 22 '15 at 13:26
  • I would ask why you are using read uncommitted in the first place since you seem to be concerned with accuracy. But for the index corruption take a look here. https://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/ – Sean Lange Sep 22 '15 at 13:28
  • I use it because of performance issues. The table is used for reading and writing by multiple transactions. But the original question was about the order of generating identity values – Random Sep 22 '15 at 13:38
  • Ugh. Using read uncommitted is NOT a magic go fast button. It has some very serious side affects. This may be a bit off your question but you need to understand what you are doing with that isolation level. It is the same thing as throwing NOLOCK on every single query. It has its place but when you care about accuracy (which is almost always outside of reports) you shouldn't use it. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Sean Lange Sep 22 '15 at 13:43
  • As for locking, you could specify a tablock hint to ensure the table is locked but why? Is it critical that each thread have their identity values in sequence with no gaps? One of the things that an identity does really well is handle concurrency. That is what so many people get wrong when they create their own. – Sean Lange Sep 22 '15 at 13:45
  • I understand the side effects of read uncommitted. As for identities, I'm not sure about the following situation: some transaction (READ COMMITTED) reads from table B when the first transaction is completed, gets the last generated identity. Later, when the second write-thansaction is completed I want to be sure that rows identities added by it are greater than previous max identity from the first transaction. – Random Sep 22 '15 at 13:54
  • Are you reinventing Change Data Capture and/or Change Tracking? SQL Server has facilities built in to allow you to track changes, and shouldn't need you to invent a new one based on `IDENTITY` values. – Damien_The_Unbeliever Sep 22 '15 at 13:57
  • Perhaps. But even considering this feature, it's not quite clear whether I'll get committed or uncommitted data and how to track the last processed change – Random Sep 22 '15 at 14:23

1 Answers1

0

It's confirmed to be possible if transaction have other operations after updating records in table A. But it's not possible with a single update command. Transaction 1 updates rows in table A, which triggers inserting rows into table B. The first range of identities was generated (e.g., 5 - 10). Then transaction 2 updates rows in table B, which triggers inserting rows into table B. The second range of identities was generated (e.g. 11 - 15). Both transactions continue executing. If the second transactions is completed and committed before the first, identities range 11 - 15 appears to be committed before the range 5 - 10.

Random
  • 3,807
  • 2
  • 30
  • 49