Transactions

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 (Atomicity,Consistency preservation,Isolation,Durability)
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.

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_id,au_lname,au_fname,phonecontract 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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi14y ago
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 (Atomicity,Consistency preservation,Isolation,Durability)
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.
0
Senthilkumar14y ago
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_id,au_lname,au_fname,phonecontract 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

0