Best way to execute multiple db commands in a transaction

[belama]

Hi,

I'm using SQL Enterprise Library (Microsoft.Practices.EnterpriseLibrary.Data.Sql) objects and I'm considering using a DbTransaction to execute multiple DbCommand(s) for inserts/updates in one transaction.

Let's say these queries take a few minutes to execute, I do not want it to prevent another process from queries the same tables. Considering I do not want to open/close multiple connections and I do not want any "locks" on my database tables is this a good way to go ahead with this?

Thanks

[thines01]

Is there a possibility the data could change during the transaction?

[belama]

There is a slight chance it might be updated but as long as the data can be fetched at the same time, I'm ok with it. At least minimize lock time.

[Momerath]

During an update, SQL Server locks the resources in question with an exclusive lock, so they can't be read.

From the documentation on transactions (italics mine):

"BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation."

One thing to keep in mind is that SQL Server has row locking capability, and tries to use the least restrictive lock that it can.

The other thing is that a single connection can issue multiple commands, you only need to do a transaction if *all* of the commands must complete successfully for the database to be updated. For example, in a banking application, any money transfer has two parts, the deduction and the addition. Both must succeed or you'll be losing (or creating) money. So you use a transaction.