What type of Transaction IsolationLevel should I use to ignore inserts but lock the selected row?

Question

I have a process that starts a transaction, inserts a record into table1, and then calls a long web service (up to 30 seconds). If the web service call fails, insert rollback (this is what we want). Here is an example of an insert (in fact, this is a few attachments to several tables, but I simplify this question):

INSERT INTO Table1 (UserId, StatusTypeId) VALUES (@UserId, 1)

I have a second process that queries Table1 from the first step as follows:

SELECT TOP 1 * FROM Table1 WHERE StatusTypeId=2

and then updates this line to the user. When process 1 is started, table 1 is locked so that process 2 will not be completed until process 1 is finished, which is a problem because a long delay is introduced when process 1 ends the web service call.

Process 1 will only insert StatusTypeId from 1, and this is also the only operation that inserts into table 1. Process 2 will only query StatusTypeId = 2. I want to tell Process 2 to ignore any inserts in Table 1, but to lock the selected row. The default isolation level for Process 2 is too much, but I have a fear that IsolationLevel.ReadUncommitted allows you to read too much dirty data. I do not want two users to start Process 2, and then accidentally get the same line.

Is there another IsolationLevel to use besides ReadUncommitted, which says to ignore inserted rows, but make sure select blocks the selected row?

Answer 1

, SELECT , , .

.

CREATE TABLE Table1
(
UserId INT PRIMARY KEY,
StatusTypeId INT,
AnotherColumn varchar(50)
)
insert into Table1
SELECT number, (LEN(type)%2)+1, newid()
FROM master.dbo.spt_values
where type='p'

1

BEGIN TRAN
INSERT INTO Table1 (UserId, StatusTypeId) VALUES (5000, 1)
WAITFOR DELAY '00:01';
ROLLBACK

()

SELECT TOP 1 * 
FROM Table1 
WHERE StatusTypeId=2 
ORDER BY AnotherColumn

, CREATE NONCLUSTERED INDEX ix ON Table1 (StatusTypeId,AnotherColumn)

Process 2, ( READPAST Process 2 , ). Remus Rusanu

BEGIN TRAN

SELECT TOP 1 * 
FROM Table1  WITH (UPDLOCK, READPAST)
WHERE StatusTypeId=2
ORDER BY AnotherColumn

/*
Rest of Process Two code here
*/
COMMIT

Answer 2

: , insert select READ COMMITTED, .

, , , , .

• Dirty Read - , - READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

• Non Repeatable Reads - , , - READ UNCOMMITTED, READ COMMITTED. REPEATABLE READ, SERIALIZABLE

• phantom rows - ​​ , , - READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE