UniDac and multiple transactions

Tomasz Andrzejewski · Post by **Tomasz Andrzejewski** » Sun 29 Jan 2012 10:34

Hello,

In my application I have to start separate transaction for each loading a dll plugin e.g:
a) user XYZ starts application
b) user XYZ opening loading new plugin and opening box form in edit mode (record id = 1)
so he have to lock record
b) user XYZ opening new box form in edit mode (record id = 2)

To inform other users about locking records I use separated table with information TableName and recordId.
I want to insert records to this table in transaction (after user processing with this record lock will be removed by rollback this transaction).

This same user (e.g XYZ) might open few box forms for different tables or different records - I have use separated transactions for it and after processing rollback correct one.

How to do this using one connection?

Tomasz Andrzejewski · Post by **Tomasz Andrzejewski** » Sun 29 Jan 2012 12:16

I use MSSQL 2008 R2 and while I tryin' to create more than one transaction got error
"multiple transactions are not supported by database"

AndreyZ · Post by **AndreyZ** » Mon 30 Jan 2012 12:15

Hello,

SQL Server does not support multiple transactions through one connection. You don't need to use a separate table for your task. To inform users about locking records, you can use the LockMode property, for example, set it to lmPessimistic. In this case, records that are being edited will be locked on the server, and other users will not be able to start editing them. When locking is enabled, SDAC starts a transaction on editing records automatically. When posting is performed, SDAC removes lock and commits a transaction.