In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.
You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP()
cluse will be able to set the flag on at most one row. By using the OUTPUT
cluse you can return this to the application automically.
I define a simple test table and populate it:
create table dbo.T(id int, IsProcessing bit default 0);
insert dbo.T(id) values (1),(2),(3),(4);
The output clause needs a table variable to receive the changed values:
declare @U table (id int);
A bit of debug code to make the "before" and "after" states obvious:
select 'Before' as B, * from dbo.T;
And the statement itself:
update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;
And the results:
select 'After' as A, * from dbo.T;
select * from @U;
This is the output from the second execution of the above.
B id IsProcessing
Before 1 1
Before 2 0
Before 3 0
Before 4 0
A id IsProcessing
After 1 1
After 2 1
After 3 0
After 4 0
id
2
The row with id=2
had gone from IsProcessing=0
to IsProcessing=1
and that id
is returned in the table variable.
With this trivial data the rows are processed in the sequence they were created. In a more complex environment the optimizer may choose any row that matches the where clause. If you have a requirement to process rows in, say, time sequence further qualification will be required.
I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.
It is of course possible that there are no rows with IsProcessing=0
at the time the statement runs. In this case the table variable will have zero rows.
To fully isolate each transaction from the other you could try sp_getapplock
. This will add overhead and reduce concurrency. You have to be careful to release the app lock as soon as you can in both success and failure scenarios.
Best Answer
For statements that are allowed to execute within an explicit transaction (i.e.
BEGIN TRAN
, or heaven forbidIMPLICIT_TRANSACTIONS
isON
), I am only aware of the following two that will not be affected by aROLLBACK
(though technically both are somewhat "cheating" in a sense):DML statements against table variables (variable, even table variables, do not participate in transactions)
statements executed via a linked server and
OPENQUERY
(even if using a loop back server definition to connect to the current instance) if you have the linked server property of "remote proc transaction promotion" disabled (so that it doesn't attempt to enlist in the current transaction).OPENQUERY
(with that one option disabled) works because it makes a separate connection. There are other, similar methods of making a separate connection within T-SQL. David Browne, in a comment, mentioned extended stored procedures. We can also add SQLCLR methods (no matter what T-SQL object type they are exposed as) to that list, but only if making a regular / external connection, and specifying "enlist=false;
" in the connection string. We can probably also add the OLE Automation stored procedures (i.e.sp_OA*
), but I am not 100% sure of these.There might be one or two statements that can be executed within an explicit transaction but cannot be rolled back, and I think I maybe ran into that many years ago, but I don't recall what it is and could have been user error (some other user, of course ;-), so you would have to consult the documentation for specifics of each statement / operation and/or test as many as you can (sometimes the documentation is incorrect ... it happens).
Not sure if this counts or not as it isn't an explicit change, but is not something that goes back to what it was before the transaction started (because it doesn't even make sense that it could): Tibor Karaszi noted in a comment that "consuming an identity/sequence value" could qualify. I have tested and confirmed that sequences do not give back the requested values.
Similarly, another value that is database state, and not specifically user data, that is not affected by a rollback is the current / last-used timestamp value (this is per-database and is returned by the system variable
@@DBTS
; also confirmed via test)Not sure if these count or not as they are neither an explicit change nor even the state of the database, but their values are not affected by a rollback: in a comment, David Browne mentioned
SESSION_CONTEXT
, which is a property of each session. Along those same lines, we can include the related / similarCONTEXT_INFO
Yes, this is the purpose of explicit transactions: to group statements together into an atomic unit of work.
Some (maybe most) yes. Some no. You probably should test. For example, here is some DDL that does roll-back:
But, conversely, here is some DDL that cannot be executed within an explicit (or implicit) transaction:
So, just take the two examples above and rework them for other statements that you are either curious about or need a definitive answer on.