0

Lets say we have a usual situation with Order_Header and Order_LineItems tables. Also, lets say we have transactions for creating, updating and selecting Orders. Like:

Create:

BEGIN TRANSACTION

INSERT INTO Order_Headers...

SET @Id = SCOPE_IDENTITY()

INSERT INTO Order_LineItems...(using @Id)

DECLARE @SomeVar INT

--just example to show update dependant on select
SELECT @SomeVar = COUNT(*) Order_Headers
WHERE OrderDate > '1-1-2017'

UPDATE Order_Headers
SET SomeValue = @SomeVar
WHERE Id = @Id

COMMIT

END TRANSACTION

On the other hand, we have transaction for getting Orders base on some criteria, for simplicity, lets say last 10:

SELECT TOP 10 * FROM Order_Headers
ORDER BY Id DESC

Could someone please say what would be correct isolation level for each transaction and shortly explain why?

[UPDATE]

  1. I want to make sure that no other session can insert rows matching WHERE OrderDate > '1-1-2017'

  2. I also want to make sure that second transaction (pure selecting orders) never pick up rows that are not fully 'done' in first transaction. Meaning those that are created in transactions INSERT part but not yet updated in UPDATE part. (i guess that is covered by READ COMMITED being default, right?)

[UPDATE 2]

i want

WHERE OrderDate > '1-1-2017'

to be the value at the begining of the transaction.

dee zg
  • 13,793
  • 10
  • 42
  • 82
  • What are you trying to guard against? Do you want to ensure that no other database session can insert Order_LineItems for `@Id`s until you commit? Do you want to prevent others from inserting rows matching `WHERE OrderDate > '1-1-2017'` until you commit? – Ben Gribaudo Jul 03 '17 at 20:52
  • Adding on to @BenGribaudo, question, do you want the result of `WHERE OrderDate > '1-1-2017'`to be value when the transaction began or when the `SELECT` query ran? – Dan Guzman Jul 03 '17 at 20:56
  • hi guys, thanks for great questions both! i have updated my question. – dee zg Jul 03 '17 at 21:01
  • @deezg, not sure I understand why you want to prevent concurrent session inserts if you can ensure the value read is at the beginning of the transaction. The desired isolation level could be achieved with row versioning instead of locking (i.e. SNAPSHOT instead of SERIALIZABLE). – Dan Guzman Jul 03 '17 at 21:18
  • @Dan well, thats a good question. Didnt think about it as related. Actually, i want simplest way to ensure i dont get conflicts in terms of wrong results with Orders. You are right, if value is ensured to be one from the begining of transaction then there is no point in preventing concurrent updates. How would you handle this situation? What would be your prefered solution? – dee zg Jul 03 '17 at 21:25
  • `SNAPSHOT` is how you could accomplish your requirement but need to be aware of the additional costs of storage and tempdb usage, In the real world, I would fix the database design to avoid storing data for unrelated orders in the order header of a particular order and run in the default `READ_COMMITTED` level :-) – Dan Guzman Jul 03 '17 at 21:35
  • @Dan, right right but here its a domain requirement that each order is dependant on some set of others. Not on those with date greater than something, that was just a dummy example to show dependency on potentially open set but dependant on some set of other orders which might not be static. Point being, of course one should never store things like these because of db design but here the reason is plain domain requirement aka business rule. – dee zg Jul 03 '17 at 22:32
  • Imagine for example your each next order has Discount field which is calculated based on time passed from last order entered. thats a good example too. or even better, on average frequency of last 10 orders. or number of orders created on even minutes within last x orders. you see where i am getting. – dee zg Jul 03 '17 at 22:33
  • i will actually post new question about this particular case – dee zg Jul 05 '17 at 14:58
  • @Dan Guzman please take a look at this question, which is somewhat modified/expanded version: https://stackoverflow.com/questions/44930171/transaction-isolation-inserts-dependant-on-previous-records-values?noredirect=1#comment76837443_44930171 – dee zg Jul 05 '17 at 15:53

1 Answers1

1

First make sure your database has enabled snapshot isolation

ALTER DATABASE YOURDB
SET ALLOW_SNAPSHOT_ISOLATION ON

First transaction requires SNAPSHOT isolation

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Second Transaction requires READ COMMITTED isolation

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
S4V1N
  • 279
  • 1
  • 7