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?


5 Years
Discussion Span
Last Post by Momerath

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.


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.

Edited by Momerath: n/a

Votes + Comments
useful info
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.