Transactions

Question

Hi I want to know that what is the transaction explain with the help of example ?

Jignesh Trivedi · Answer

hi A transaction in SQL (structured query language) is a larger unit of database processing that contains one or more database access operations like insertion deletion retrieval and modification operations. These transaction are required to fulfil certain characteristics like ACID (AtomicityConsistency preservationIsolationDurability) In other word Transaction means being able to execute multiple statements as a single unit so that either all the statements are executed or none are executed. Example: BEGIN TRAN _test BEGIN TRY -- your DML COMMIT TRAN _test END TRY BEGIN CATCH ROLLBACK TRAN _test END CATCH please refer http://msdn.microsoft.com/en-us/library/ms188929.aspx hope this help.

Senthilkumar · Answer

Hi Transactions are an inherent part of any application that collects or manipulates data. Transaction is a set of statements (called as atomic) that either all steps succeed or all steps fail Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails the transaction fails. Therefore a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL statements into a single transaction using the following statements: • Begin Transaction • Rollback Transaction • Commit Transaction If anything goes wrong with any of the grouped statements all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction all changes are recorded together in the database. In SQL Server terminology we say that these changes are committed to the database. Transaction is a set of statements (called as atomic) that either all steps succeed or all steps fail Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they're finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. For example You have more number of records in a table and want to insert. You can't say when it will break. If something happens in between then very difficult to find upto where it will inserted. You need staus some thing like it should insert all otherwise none to insert again. BEGIN TRAN INSERT INTO [authorstmp]([au_id] [au_lname] [au_fname] [phone] [contract]) SELECT au_idau_lnameau_fnamephonecontract FROM Authors IF @@ERROR 0 BEGIN ROLLBACK TRAN END ELSE BEGIN COMMIT TRAN END Here it will start the transaction and if there is no error then it will commit other wise rollback. For more info: http://msdn.microsoft.com/en-us/library/ms188929.aspx http://www.sqlteam.com/article/introduction-to-transactions