One reason an SQL database is chosen for data persistence is the ability to have ACID transactions (If you are not familiar with the acronym ACID you can do a Google search for more details). Not all applications require ACID capabilities, nor do all data engines provide ACID capabilities. There is a lot of overhead required to assure a transaction has ACID properties, resulting in slower performance assuring the ACID properties of your work.
ACID is built into the use of Transactions in the SQL language. TSQL (for Microsoft SQL Server) allows you to begin a transaction, execute one or more statements, and then COMMIT the transaction to save all modifications permanently, or ROLLBACK the transaction, removing all intended changes to the database. While the transaction is open, because you have not called COMMIT or ROLLBACK, all of your modifications are not made available to other users.
When you write a stored procedure, you can handle your own transactions within the stored procedure. If you don’t implement transactions in your stored procedure, then the stored procedure utilizes the transaction settings established for the connection to the database. Most applications use the implicit transactions established for any connection to the database. Implicit transactions work as if each query executed has its own distinct transaction. If the query is successful, the change is committed to the database upon completion.
Often, your database will have a need for multiple SQL commands to complete as a single Unit of Work. All of the commands must complete successfully, or be rolled back completely. You want to have all changes, or no changes at all. What are your options under these circumstances? One method is to have one or more stored procedures or queries called within the scope of a single transaction. This can be difficult to implement in that you must pass all of the data in a single call from the client. If this is possible, a single database call may be adequate.
Another implementation may be to use something like ADO.Net, and allow it to use distributed transaction coordination. What this means is that you create a transaction in your ADO objects when calling the database. ADO then coordinates with SQL Server so that no data is committed until you explicitly tell ADO to commit the changes. You may make multiple calls to SQL, and each one is held separate from other users until your final ADO commit is executed. ADO then passes the Commit through to each of the previous SQL commands, and your changes become permanent. ADO can also rollback changes.
SQL Server transactions are the most efficient. ADO Transactions are the most flexible. Using ORM tools, if you want to implement the use of transactions across more than one modification, it is easiest to implement some sort of Unit Of Work pattern.
Cheers,
Ben