How do I set the correct transaction level?

Question

How do I set the correct transaction level?

ado.net dapper isolation-level sql-server

Question

I am using Dapper on ADO.NET. So at present I am doing the following:

using (IDbConnection conn = new SqlConnection("MyConnectionString")))
{
conn.Open());
using (IDbTransaction transaction = conn.BeginTransaction())
{
// ...

However, there are various levels of transactions that can be set. I think this is the various settings.

My first question is how do I set the transaction level (where I am using Dapper)?

My second question is what is the correct level for each of the following cases? In each of these cases we have multiple instances of a web worker (Azure) service running that will be hitting the DB at the same time.

I need to run monthly charges on subscriptions. So in a transaction I need to read a record and if it's due for a charge create the invoice record and mark the record as processed. Any other read of that record for the same purpose needs to fail. But any other reads of that record that are just using it to verify that it is active need to succeed.

So what transaction do I use for the access that will be updating the processed column? And what transaction do I use for the other access that just needs to verify that the record is active?

In this case it's fine if a conflict causes the charge to not be run (we'll get it the next day). But it is critical that we not charge someone twice. And it is critical that the read to verify that the record is active succeed immediately while the other operation is in its transaction.

I need to update a record where I am setting just a couple of columns. One use case is I set a new password hash for a user record. It's fine if other access occurs during this except for deleting the record (I think that's the only problem use case). If another web service is also updating that's the user's problem for doing this in 2 places simultaneously.

But it's key that the record stay consistent. And this includes the use case of "set NumUses = NumUses + @ParamNum" so it needs to treat the read, calculation, write of the column value as an atomic action. And if I am setting 3 column values, they all get written together.

David Thielen

View more on Stack Overflow

Alex · Accepted Answer · 9/14/2018 2:32:35 AM

1) Assuming that Invoicing process is an SP with multiple statements your best bet is to create another "lock" table to store the fact that invoicing job is already running e.g.

CREATE TABLE InvoicingJob( JobStarted DATETIME, IsRunning BIT NOT NULL )

-- Table will only ever have one record
INSERT INTO InvoicingJob
SELECT NULL, 0


EXEC InvoicingProcess

ALTER PROCEDURE InvoicingProcess
AS
BEGIN
    DECLARE @InvoicingJob TABLE( IsRunning BIT )

    -- Try to aquire lock
    UPDATE InvoicingJob WITH( TABLOCK )
    SET JobStarted = GETDATE(), IsRunning = 1
    OUTPUT INSERTED.IsRunning INTO @InvoicingJob( IsRunning )
    WHERE IsRunning = 0
        -- job has been running for more than a day i.e. likely crashed without releasing a lock
        -- OR ( IsRunning = 1 AND JobStarted <= DATEADD( DAY, -1, GETDATE()) 

    IF NOT EXISTS( SELECT * FROM @InvoicingJob )
    BEGIN
        PRINT 'Another Job is already running'
        RETURN
    END
    ELSE
        RAISERROR( 'Start Job', 0, 0 ) WITH NOWAIT


    -- Do invoicing tasks
    WAITFOR DELAY '00:01:00' -- to simulate execution time

    -- Release lock
    UPDATE InvoicingJob
    SET IsRunning = 0
END

2) Read about how transactions work: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

You second question is quite broad.

How do I set the correct transaction level?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator