Menu
Consider a regular web application doing mostly form-based CRUD operations over SQL database. Should there be explicit transaction management in such web application? Or should it simply use autocommit mode? And if doing transactions, is "transaction per request" sufficient?
747
Transactions are to be used to ensure that the database is always in a consistent state. In general, unless there is a good reason not to use them (long running process for instance), use them.
Transactions should be used when there is the possibility that either failure to complete or someone else reading or writing in the middle of your task could cause damage to the data. These include but are not limited to: Reading from a table for subsequent deletion. Writing related data to multiple tables.
SQL Transaction gives you the “power to return back to a safe state if some error happens in the middle of your SQL Code”. For example, suppose in your Stored Procedure you are running an Insert statement followed by Update statement.
Implicit transactions are automatically handled by the Integration Server transaction manager. When you define an explicit transaction, you define the start-on-completion boundaries of the transaction. As such, implicit and explicit transactions need to be created and managed differently.
I would only use explicit transactions when you're doing things that are actually transactional, e.g., issuing several SQL commands that are highly interrelated. I guess the classic example of this is a banking application -- withdrawing money from one account and depositing it in another account must always succeeed or fail as a batch, otherwise someone gets ripped off!
We use transactions on SO, but only sparingly. Most of our database updates are standalone and atomic. Very few have the properties of the banking example above.
72
I strongly recommend using transaction mode to safe data integrity because autocommit mode can cause partial data saving.
30
This is usually handled for me at the database interface layer - The web application rarely calls multiple stored procedures within a transaction. It usually calls a single stored procedure which manages the entire transaction, so the web application only needs to worry about whether it fails.
Usually the web application is not allowed access to other things (tables, views, internal stored procedures) which could allow the database to be in an invalid state if they were attempted without being wrapped in a transaction initiated at the connection level by the client prior to their calls.
There are exceptions to this where a transaction is initiated by the web application, but they are generally few and far between.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
