Need assistance in using Dapper to lock transaction

Question

User1997592189 posted

HI All,

I have one task to lock DB transaction while inserting one row into table. I mean let us say i am running two parallel tasks at a time and in first task i am trying to insert record into table and second task i need to select records from same table and database. My requirement here is to restrict/lock the DB table until the first task to complete. So that after first task successfully completed second task will run and fetch the new inserted record as well. 

I tried with transaction scope but no luck. I am using here Dapper to insert/fetch records from sql server database. Any suggestions please?

Thanks & Regards,

J.V.Srikar.

Answer 1

User415553908 posted

By default SQL Server (I assume this is your DB engine of choice?) will not block the whole table when you insert, but you can hint it to with TABLOCKX hint. You will also want to indicate SQL server to hold that lock for the duration of your first transaction by HOLDLOCK hint.

INSERT INTO a (c1, c2, c3) WITH(TABLOCKX, HOLDLOCK) VALUES(1, 2, 3)

Alternatively, consider wrapping the code that inserts/selects data into the DB into a lock statement to ensure processing order (but not knowing your specifics it's a bit hard to say whether this will apply to you)

Answer 2

User1997592189 posted

Hi Timur,

Thanks for your quick response. I actually need to lock the dapper insert transaction in c# code. I am using Task.WaitAll(Task1, Task2) for testing purpose where Task1 contains the logic to insert the record into DB table and Task2 will try to fetch the records from same table. Here both tasks are running simultaneously and due to which before Task1 completes I am able to get records using Task2 and this result does not contain the record which is going to insert using Task1. That's why I need to lock the DB table using C# code so that until Task1 completes no other transaction should be allowed on same table. Please suggest.

Answer 3

User415553908 posted

so your Task1 - what sort of code do you have to insert the data?

if it's plain SQL - try add the table hints as I described above - this should ensure the data gets written 

Answer 4

User475983607 posted

I have one task to lock DB transaction while inserting one row into table. I mean let us say i am running two parallel tasks at a time and in first task i am trying to insert record into table and second task i need to select records from same table and database. My requirement here is to restrict/lock the DB table until the first task to complete. So that after first task successfully completed second task will run and fetch the new inserted record as well.

Your design will not work. 

Use standard programming patterns.  Create a single script that inserts the record and returns the ID of the new record.  Please read the official documentation.

https://dapper-tutorial.net/insert