3

Using WCF and the Microsoft Distributed Transaction Co-ordinator, is it possible to implement a long-running transaction against a single database? I've drawn out a simplified version of the scenario in question.

Is there a time limit that transactions have to occur within?

Assuming the DB is SQL Server or Oracle, would there be locking that would occur during the transaction? Would it be at the row level or table level?

enter image description here

Scampbell
  • 1,535
  • 14
  • 24

1 Answers1

3

Database transactions should be short-lived. See for instance Are long-living transactions acceptable?

So, while WCF and DTC will allow you to distribute the transactions across Machines and through SOAP calls, this is not a good idea for the scenario you are describing.

Use compensating transaction logic instead, e.g. remove the inserted Emp1 and Emp2 when the creation of Emp3 fails. Or manually mark the inserted Emp1 and Emp2 rows as still-in-transaction until they've all been successfully written. Or collect the actions in the WCF Service and then (transactionally) commit them to the Database when all information has become available.

Which solution is best depends on the details of your use case.

Community
  • 1
  • 1
flup
  • 26,937
  • 7
  • 52
  • 74
  • 1
    What are your reasons to not use the transaction and reliable messaging features of WCF? – John Saunders Apr 10 '14 at 22:14
  • It would help to backup the point about the locking described in the the link. That seems to me like the largest reason to not do this, rather than the connection issues. – Scampbell Apr 10 '14 at 23:54
  • 1
    @JohnSaunders The bit where it says "Time passes" means that the transactions will be long-lived and this is A Bad Thing. For short-lived transactions, I've successfully used the DTC and WS-Transactions using WCF and can recommend it. – flup Apr 11 '14 at 07:46
  • @Scampbell The linked question discusses quite extensively why it's a bad idea. Locking is indeed the biggest pain, you'd have to toy around with the isolation level if you want to read around the inserted but not yet committed Emp rows. – flup Apr 11 '14 at 07:51