How do I set the correct transaction level?

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.

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.

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.

1 Answer
1

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.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.