In this blog we will learn about database transaction;
Table of content
Managing Transaction Processing
Rollback: The process of undoing a SQL statement
Commit Transaction Processing
SavePoint Transaction Processing
Managing Transaction Processing
Transaction processing is used to maintain database integrity by ensuring that batches of SQL.
Within your application, a single SQL INSERT, UPDATE, or DELETE statement may not in itself constitute a complete transaction.
In such cases, you use transaction processing commands to define the sequence of operations that forms a complete transaction. One command marks the beginning of the transaction; after a sequence of possibly many commands, another command marks the end of the transaction.
Under normal circumstances, the transaction executes in its entirety. If a program error or system malfunction leads to an incomplete transaction, then the part of the transaction that was completed is rolled back.
This is where Transaction processing is used. This technique is used to make sure a set of operations is completely executed in a batch. This ensures that the entire batch was executed and not just a partial. They must be executed in their entirely or not at all. If an error occurs a rollback occurs to restore the data.
When using transaction processing there are some keywords you will be using:
Transaction: A block of code
Rollback: The process of undoing a SQL statement
Commit: Writing unsaved SQL statements to database
Savepoint: Temporary placeholder in a transaction set to which you can issue a rollback
You can only use Transaction processing to manage INSERT, UPDATE, or DELETE statements.
Rollback: The process of undoing a SQL statement
In this example, a DELETE operation is performed and then undone using a ROLLBACK statement. Although not the most useful example, it does demonstrate that, within a transaction block, DELETE operations are never final.
Syntax
Drop Table Orders2
Select * Into Orders2
FROM Orders
Select *
FROM Orders2
BEGIN
TRANSACTION
DELETE
FROM Orders2
ROLLBACK
Results
(5 rows affected)
(5 rows affected)
(5 rows affected)
Total execution time: 00:00:00.017
EXAMPLE 1:
The following example demonstrates the use of transaction processing. We first select the data, set the begin transaction, delete the data, set when rollback should occur and finally return the data. It basically undo’s the transaction.
Syntax
--What is in orderitems?
Select
*
From
orderitems;
Results
order_num order_item prod_id quantity item_pric
20005 1 BR01 100 5.49
20005 2 BR03 100 10.99
20006 1 BR01 20 5.99
20006 2 BR02 10 8.99
20006 3 BR03 10 11.99
Syntax
--Start the transaction
BEGIN TRANSACTION
--Delete all rows
Delete
From
orderitems
--Verify that rows are gone
Select
*
From
orderitems;
--Now rollback the transaction
ROLLBACK; Select
*
From
Orderitems
Results
order_num order_item prod_id quantity item_price
20005 1 BR01 100 5.49 20005 2 BR03 100 10.99
20006 1 BR01 20 5.99
20006 2 BR02 10 8.99
20006 3 BR03 10 11.99
EXAMPLE 2:
The following example demonstrates the use of transaction processing. We first set the begin transaction, and then set when rollback should occur. In this example, we use an if condition. If the condition is false (0) then committed successful else print out error message.
Syntax
BEGIN TRANSACTION
IF @@ERROR <> 1
BEGIN
PRINT @@ERROR
PRINT 'ERROR IN SCRIPT'
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMITTED SUCCESSFULLY'
END
GO Results
0
ERROR IN SCRIPT
Total execution time: 00:00:00.002
Syntax
BEGIN TRANSACTION
IF @@ERROR <> 0
BEGIN
PRINT @@ERROR
PRINT 'ERROR IN SCRIPT'
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMITTED SUCCESSFULLY'
END
GO
Results
COMMITTED SUCCESSFULLY
Total execution time: 00:00:00.002
Commit Transaction Processing
When you execute a SQL select statement it is written directly to the database. This is known as an autocommit. The SQL code within a transaction block does not commit automatically. You need to use the Commit statement to write changes to the database. These changes will only if no errors occur.
To force an explicit commit, the COMMIT statement is used.
EXAMPLE:
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION EXAMPLE:
The following example demonstrates the use of transaction processing. Here we are attempting to delete order number 20010. Notice that we are deleting from two separate tables. This statement will only occur if there are no errors.
Syntax
--Start the transaction
BEGIN TRANSACTION
SET IDENTITY_INSERT orderitems ON
--Delete all rows
Delete From orderitems Where order_num = 20010;
Delete From orders Where order_num = 20010;
SET IDENTITY_INSERT orderitems OFF
--Commit the delete statement only if no error occu
Commit; Simple ROLLBACK and COMMIT statements enable you to write or undo an entire transaction.
To roll back to a certain point you need to create SAVEPOINT.
This SAVEPOINT will tell the DBMS when to roll back and where to roll back to.
To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders.
In SQL, these placeholders are called savepoints.
To create one in MySQL and Oracle, the SAVEPOINT statement is used, as follows:
SAVEPOINT delete1; EXAMPLE:
This statement will only occur if there are no errors.
Here are a set of four INSERT statements enclosed within a transaction block. A savepoint is defined after the first INSERT so that, if any of the subsequent INSERT operations fail, the transaction is only rolled back that far.
Syntax:
COMMIT TRANSACTION
Results
(1 row affected)
(1 row affected)
(1 row affected)
Msg 2627, Level 14, State 1, Line 14
Violation of PRIMARY KEY constraint 'PK_OrderItems'. Cannot insert duplicate key in object 'dbo.OrderItems'. The duplicate key value is (20006, 3).
Msg 3621, Level 0, State 0, Line 14
The statement has been terminated.
Total execution time: 00:00:00.011
If you need any programming assignment help in Database, Database project or Database homework then we are ready to help you.
Send your request at realcode4you@gmail.com and get instant help with an affordable price.
We are always focus to delivered unique or without plagiarism code which is written by our highly educated professional which provide well structured code within your given time frame.
If you are looking other programming language help like C, C++, Java, Python, PHP, Asp.Net, NodeJs, ReactJs, etc. with the different types of databases like MySQL, MongoDB, SQL Server, Oracle, etc. then also contact us.