What is the correct isolation level for Order head

问题:

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.

回答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