2

This question is related/came from discussion about another thing: What is the correct isolation level for Order header - Order lines transactions?

Imagine scenario where we have usual Orders_Headers and Orders_LineItems tables. Lets say also that we have a special business rules that say:

  1. Each order has Discount field which is calculated based on time passed from last order entered

  2. Each next order Discount field is calculated specially if there has been more than X order in last Y hours.

  3. Each next order Discount field is calculated specially if average frequency of last 10 orders was higher than x per minute.

  4. Each next order Discount field is calculated specially

Point here is to show that every Order is dependant on previous ones and isolation level is crucial.

We have a transaction (just logic of the code shown):

BEGIN TRANSACTION

INSERT INTO Order_Headers...

SET @Id = SCOPE_IDENTITY()

INSERT INTO Order_LineItems...(using @Id)

DECLARE @SomeVar INT

--just example to show selecting previous x orders
--needed to calculate Discount value for new Order
SELECT @SomeVar = COUNT(*) Order_Headers
WHERE ArbitraryCriteria

UPDATE Order_Headers
SET Discount= UDF(@SomeVar)
WHERE Id = @Id

COMMIT

END TRANSACTION

We also have another transaction to read orders:

SELECT TOP 10 * FROM Order_Headers
ORDER BY Id DESC

QUESTIONS

  1. Is SNAPSHOT isolation level for first transaction and READ COMMITED for second appropriate levels?

  2. Is there a better way of approaching CREATE/UPDATE transaction or is this the way to do it?

dee zg
  • 13,793
  • 10
  • 42
  • 82
  • Not trying to be rude... is your database set up to allow snapshot isolation? Do you know what it is? I'm not sure how much you know about it or if you've just seen that keyword while googling for answers. – SqlZim Jul 05 '17 at 15:24
  • not rudness taken;). yes, it has been set to allow snapshot isolation. i know what it is. not an expert but i know basic ideas behind it. that is precisely the reason why i ask what is the way to do this properly because i understand theoretically what each isolation level means but have no real world experience whith examples like this and don't know all the caveats of different approaches. hope that makes sense? if not, please point a finger to what doesn't make sense so i could clarify more. – dee zg Jul 05 '17 at 15:28
  • after reading the comments on your previous question, I would recommend you respond to @DanGuzman on that post with the link to this question to give him the opportunity to expand upon his comments. – SqlZim Jul 05 '17 at 15:51
  • thank you for suggestion. also, if you have your thoughts/solutions to this question i would love to hear it. – dee zg Jul 05 '17 at 15:52

2 Answers2

2

The problem with snapshot is not about inserting/reading (which i assume you decided to use). Its about updates, that you should be a concerned.

Snapshot isolation levels are using row versioning. Which means any time you insert/update/delete row, those rows get duplicated in tempdb(version store, location for those kinds of rows), and increase its size by 14 bytes with a versioning tag so that your newly started transaction can read a row from the last committed transaction. Keep in mind that these resized rows will stay as they are until you rebuild the index.

This should be an indicator ,that if your table is really busy, your indexes will be defragmented much faster and it will add certain amount of % overhead on your temp.So keep that in mind.

What is even bigger concern here are updates, as i mentioned.

Any time you insert/delete/update row, you will get exclusive locks on those rows (object later),and since you snapshot is using row versioning, inserts from another transaction are adding exclusive locks on a NEW row, and that is not a problem.However if you try to update an existing row and session 2 tries to acquire X lock on that row, it will fail because session 1 already has X lock on it, and this is where you will get this message:

enter image description here

Read Committed and Serializable have covered these issues well, so you might wanna take that approach and test all solutions before you actually implement it. Remember all transactions will cause blocking on updates, and snapshot/read comitted snapshot will simply fail.

Me personally would`ve used read committed snapshot and altered procedure , to rerun in catch block N amount of times, but hey that has flaws as well !

Community
  • 1
  • 1
S4V1N
  • 279
  • 1
  • 7
  • Thank you for your response. First of all, just to say i haven't decided anything. It just seems to me that i can't get around snapshot/serialize isolation level because of phantom reads. I would love to hear other ways if there are any. Also, I am not sure if i read your answer correctly but it seems like you don't like the direction of snapshot (my impression might be wrong here) and i don't have a clear understanding why. One thing i didn't mention, there will be many orders of magnitudes more SELECTS than INSERT/UPDATES on the tables in question. I would not consider them INSERT intensive – dee zg Jul 05 '17 at 16:36
  • 1
    To make it short. If you use snapshot and run 2 of those transactions at the same time, later one will fail and you will get an error. I recommended read committed snapshot, which is different from read committed and it does not have phantom reads, it will cause deadlock and you can solve it by rerunning it, instead of getting an error. Or you can simply use serializable and prevent any of those from happening (but have lower much concurrency) – S4V1N Jul 05 '17 at 16:43
1

The serializable option:

Using a pessimistic locking strategy by way of the updlock and serializable table hints to acquire a key range lock specified by the where criteria (backed by a supporting index to lock only the range necessary for the query):

declare @Id int, @SomeVar int;
begin tran;

  select @SomeVar = count(OrderDate) 
  from Order_Headers with (updlock,serializable) 
  where OrderDate >= '20170101';

  insert into Order_Headers (OrderDate, SomeVar)
    select sysdatetime(), @SomeVar;

  set @Id = scope_identity();

  insert into Order_LineItems (id,cols)
    select @Id, cols
    from @TableValuedParameter;

commit tran;

A good guide to the why and how of using the updlock and serializable table hints to lock a key range with a select, and why you need both, is covered in Sam Saffron''s upsert (update/insert) patterns.

Reference:

SqlZim
  • 37,248
  • 6
  • 41
  • 59
  • Thank you so much for Sam Saffron's upsert read! So many useful infos in rather short article. UPDLOCK does seem like much safer solution with respect to deadlocks. I think this is the direction i will use. Thanks also for effort putting additional references into your answer! I will also wait a bit if Dan Guzman shows up with his additions to previous comments but so far, your answer is a candidate for being accepted. Thanks again! – dee zg Jul 05 '17 at 17:26
  • 2
    @deezg, refactoring your code as did @SqlZim along statement-level serializable using either `UPDLOCK,SERIALIZABLE` or `UPDLOCK,HOLDLOCK` hints will provide the behavior you describe. Be aware this method will effectively single-thread inserts/updates but reduced concurrency is the cost of avoiding deadlock retries. – Dan Guzman Jul 06 '17 at 02:11