top of page

Database Transaction Assignment Help | Transaction In SQL Server



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; 

SavePoint​ ​Transaction​ ​Processing

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.

Commentaires


bottom of page