none
How do I set the correct transaction level? RRS feed

  • 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.

    1. 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.

    2. 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.

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Thursday, September 13, 2018 4:44 PM

All replies

  • Hi DavidThi808,

    Please refer to the following thread. which said that we could not set transaction level by using ado.net.

    Isolation level is aligned with transaction. When we new a transaction, the new created transaction has its own isolation level. 
    Howeven, when we reuse a transaction, after the transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ceb45036-518f-44bb-b2f2-cd5c99f973d9/transaction-isolation-level-in-adonet?forum=sqldataaccess

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 14, 2018 3:29 AM
    Moderator
  • Thank you, that answers my first question about how to set the transaction isolation level.

    Can you also tell me what isolation level I want for the 2 scenarios above?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Friday, September 14, 2018 11:52 AM
  • Hi DavidThi808,

    For second scenarios, I would suggest that you could lock the row before you update the records.  like this:

    SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 18, 2018 2:42 AM
    Moderator