I'm trying to understand how transaction atomicity in MS SQL Server in conjunction with C#'s IDbTransaction (and Dapper, albeit I assume that makes little difference) works.
Let's say, I want to insert something into a database, but only if the table has exactly 42 records right before my insert. Let's look at 3 naive examples (slightly psuedo-code, can't test this at the moment, but should demonstrate the general question/idea):
IDbConnection connection; // some connection to a MSSQL database
// Example 1
var transaction = connection.BeginTransaction();
int count = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM table");
if (count == 42)
{
// Are we sure it's still 42? Could something else swoop in between the statements, modify "table", which now doesn't have 42 records anymore?
await connection.ExecuteAsync("INSERT INTO table ... etc ...");
}
transaction.Commit();
// Example 2
transaction = connection.BeginTransaction();
await connection.ExecuteAsync("DECLARE @tableCount As INTEGER = SELECT COUNT(*) FROM table");
// Same question. Could something swoop in between the above and the below statement?
await connection.ExecuteAsync("IF @tableCount = 42 INSERT INTO table ... etc ...");
transaction.Commit();
// Example 3.
transaction = connection.BeginTransaction();
await connection.ExecuteAsync("IF (SELECT COUNT(*) FROM table) = 42 INSERT INTO table ... etc ...");
transaction.Commit();
As far as I know, example 3 should work.
What about the others though? Are they guaranteed to be quasi-atomic too, or could we end up in a scenario where we insert data, even though the table has more/less than 42 records at the time of inserting?
I've read up a bit on IsolationLevel, but am none the wiser yet.
The only way example 1 would be guaranteed to behave correctly, I believe, is if the database were to lock the table for the entire duration of a transaction as soon as it's first touched (read), in this case - which sounds unlikely to me, but what do I know.
If example 1 and/or 2 are not atomic, is there any way to make them be atomic (other than having everything in a single query, aka example 3) - some particular transaction isolation level, or any other technique on the database side)
question from:https://stackoverflow.com/questions/66055588/database-transaction-atomicity
