May 30, 2008 at 9:22 am
Where is the best place to handle transactions when writing or updating records from an application? Should the BEGINTRANS...COMMITTRANS syntax be used in ADO or should a parameter list be passed to the stored procedure and split out IDs to be upserted in BEGIN TRANS...COMMIT TRANS?
May 30, 2008 at 10:27 am
Purists are going to say at the database. I've mostly seen it done at the application level. You just have to make sure that the developers are with it enough to isolate the data access code. This means that a transaction is not dependent on the users clicking "OK" somewhere or something.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 30, 2008 at 12:00 pm
I would tend to say control the transactions at the lowest level possible. If you have several statements in a single procedure that need to be atomic, control the transaction there. If you have two procedures being called individually from an application and they need to be atomic, handle the transaction in the application. You want to always keep transactions to the shortest amount of time possible, so handling transactions from the application when you do not have to increases risk of resource blocking for no reason.
That being said, either can work fine as long as you control the transactions properly.
I do tend to stay away from DTC as much as possible because it adds a layer of additional complexity to transactions and brings with this complexity a lot of additional potential failure points.
June 2, 2008 at 10:54 am
Definitely in the client.
However, it most be done correctly.
For example, you have an input form with master/detail tables. When user saves the data (an OK button), you start transaction, generate and execute all needed statements, if all goes well commit otherwise rollback. This approach is simple, flexible and portable. Starting the transaction when opening the form would be very bad.
If this is not possible (no help from dev tool, or some other clients that can't implements this will run same transaction,...), create a stored proc that handles updates and the transaction, but it can become quite complex.
I've read that most of times developers rely on autocommit and do not manage transactions. It's ok for simple transactions, but you rarely have only simple cases.
June 3, 2008 at 7:33 am
Not that definitly at the client 😉
If you are sure that you code will reach the commit or rollback statement at the client is a choice BUT.... if you are not sure (for in let's say a web application) than always in the stored procedure.
This complicates combining other 'transactional' wishes at the OS level for example but it does gives a good garanty that the database is in the right state.
best regards...
June 3, 2008 at 8:42 am
In web setup the client application is most of times browser. However, the server side is a client for sql server, where you implement update and transaction logic. In ASP it's not so simple and such cases are covered by "if this is not possible..." part.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply