0

I am trying to do the following in a highly concurrent system written in C#.

  1. Aquire row-lock on a data-item in transaction scope
  2. Do a lot of db-interaction
  3. if anyhing goes wrong
    • Update locked data-item, that processing failed
    • Commit update
    • Roll back any changes in step 2 and release lock on data item

However - I have no idea on how to achieve this, without releasing the item lock before doing the update. My current (bad ) solution is this:

  1. Aquire row-lock on data-item in transaction scope
  2. Do a lot of db-interaction
  3. if anyhing goes wrong
    • Roll back any changes in step 2 and release lock on data item
    • Reaquire lock on data-item in new transaction scope
    • Update locked data-item, that processing failed
    • Commit update

The problem with this solution, is that I release the lock, so that another process can pick up the item before I have had a change to write to the item that processing has failed.

Is there any way to create a nested transaction scope, that can commit on an item locked by an outer transaction scope, while still allowing the outer scope to roll back?

It appears that all my trouble comes from the fact, that the outer scope has a lock on the item that the inner scope want to update.

Slind
  • 113
  • 1
  • 7
  • 1
    http://stackoverflow.com/questions/224689/transactions-in-net#224702 – C.M. Mar 07 '13 at 18:59
  • I don't think this is possible. Inner transactions that are rolled back will also roll back uncommitted outer transactions. – supergrady Mar 07 '13 at 20:19

1 Answers1

0

The way I understand the question, what you ask for is not possible.

Is there anything preventing you from using a pattern similar to this?

begin outer transaction

  //do some stuff

  bool failedFlag = false;

  begin inner transaction

    // do some more stuff

    if (failed)
      failedFlag = true
      roll back inner transaction
    else
      commit inner transaction
  end inner

  if (failedFlag)
    update locked items with failed status

commit outer transaction
500 - Internal Server Error
  • 28,327
  • 8
  • 59
  • 66
  • So if I understand your code correct, you would let the outer scope take the lock and do any updates to the locked item. While an inner scope that could commit and rollback independent of the outer scope, do all the operations on the database that do not involve the locked row? – Slind Mar 07 '13 at 21:37
  • Yes, I think that's what I mean ;) One clarification: The inner scope cannot really 'commit and rollback independent of the outer scope' - it is semantically nested, so if the outer transaction rolls back the inner is rolled back with it. OTOH, if the inner rolls back its changes, the outer transaction can still commit the changes that were made outside the inner scope. – 500 - Internal Server Error Mar 07 '13 at 21:42