Home > DeveloperSection > Beginner > Transaction Control Language:

Transaction Control Language:


Database Database 
Ratings:
0 Comment(s)
 15103  View(s)
Rate this:

Transaction Control Language in SQL

Transaction Control Language:

Transaction Control Language (TCL) is playing an important role in SQL. Transaction control language is used for statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. There are lots of TCL commands which are used in SQL in which some are namely defined as follows:

Commit:   Commit command is used for save work done in database. It is the responsibilities of the Transact-SQL programmer to issue commit transaction only at a point when all data referenced by the transaction is logically correct. You cannot roll back a transaction after a commit transaction statement is issued because the data modifications have been made a permanent part of the database.

Commit a transaction:

Syntax: Demonstration of using commit in SQL server transaction

---- SYNTAX DEMONSTRATION OF COMMIT TRANSACTION IN SQL

COMMIT [TRAN | TRANSACTION] [TRANSACTION_NAME | TRANSACTION_VARIABLE_NAME]

Example: Demonstration of using commit in SQL server transaction

---- DEMONSTRATION OF COMMIT IN SQL TRANSACTION-----

BEGIN TRANSACTION T1

GO

--- UPDATE NAME COLUMN OF USERLOGIN TABLE 

UPDATE Userlogin SET Name ='James Baswar' WHERE ID = '101'

GO

COMMIT TRANSACTION T1; ----- COMMIT TRANSACTION T1

GO

Commit a nested transaction:

----- DEMONSTRATION OF COMMIT A NESTED TRANSACTION

 

CREATE TABLE TESTTRANSACTION(TRACOUNT INT PRIMARY KEY, TRANNAME VARCHAR(10))

GO

BEGIN TRANSACTION T1 ;

GO

INSERT INTO TESTTRANSACTION VALUES (1,'T1')

----- @@TRANCOUNT RETURNS NUMBER OF ACTIVE TRANSACTION IN CURRENT CONNECTION

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

BEGIN TRANSACTION T2;

GO

INSERT INTO TESTTRANSACTION VALUES (2,'T2')

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

BEGIN TRANSACTION T3;

GO

INSERT INTO TESTTRANSACTION VALUES (3,'T3')

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

COMMIT TRANSACTION  T3;

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

COMMIT TRANSACTION T2;

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

COMMIT TRANSACTION T1;

PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))

GO

Transaction Control Language in SQL

Savepoint:  Savepoint command is used for identify a point in a transaction to which you can later roll back. Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.

Syntax:  Demonstration of using Savepoint in SQL server transaction

---- SYNTAX DEMONSTRATION OF USING SAVE POINT IN SQL SERVER TRANSACTION

SAVE [TRAN | TRANSACTION ] [SQVEPOINT_NAME | SAVEPOINT_VARIABLE_NAME]

 

Example: Demonstration of using Savepoint in SQL server transaction

---- DEMONSTRATION OF USING SAVE POINT IN SQL SERVER TRANSACTION

 

CREATE TABLE TESTSAVEPOINT(PRODUCT_ID INT PRIMARY KEY, PRODUCT_NAME VARCHAR(50))

GO

BEGIN TRANSACTION T1;

GO

INSERT INTO TESTSAVEPOINT VALUES (1001 , 'MOUSE')

INSERT INTO TESTSAVEPOINT VALUES (1002 , 'KEYBOARD')

UPDATE TESTSAVEPOINT SET PRODUCT_NAME = 'MONITOR' WHERE PRODUCT_ID = 1002

 

GO

SAVE TRANSACTION SAVEP1; ---------- CREATING FIRST SAVEPOINT SAVEP1

GO

INSERT INTO TESTSAVEPOINT VALUES (1003, 'KEYBOARD')

INSERT INTO TESTSAVEPOINT VALUES (1004, 'PRINTER')

GO

SAVE TRANSACTION SAVEP2;      ---------- CREATING FIRST SAVEPOINT SAVEP1

GO

INSERT INTO TESTSAVEPOINT VALUES (1005, 'SCANNER')

UPDATE TESTSAVEPOINT SET PRODUCT_NAME = 'CPU' WHERE PRODUCT_ID = 1005

 

ROLLBACK TRANSACTION SAVEP2 ---- TRANSACTION ROLLBACKED TO SAVEPOINT SAVEP2 SO IT'S VALUE NOT INSERTED INTO TABLE 

INSERT INTO TESTSAVEPOINT VALUES (1006,'SCANNER')

COMMIT TRANSACTION T1;

Transaction Control Language in SQL

Rollback: Rollback command is used for restore database to original since last commit. Rollback transaction erases all data modifications made from the start of the transaction or to a savepoint. The ROLLBACK statement in SQL cancels the proposed changes in a pending database transaction. The transaction can be rolled back completely by specifying the transaction name in the ROLLBACK statement.

Syntax: Demonstration using rollback in SQL transaction

-----SYNTAX DEMONSTRATION OF USING ROLLBACK IN TRANSACTION SQL QUERY

ROLLBACK [TRAN | TRANSACTION ] [SAVEPOINT_NAME | SAVEPOINT_VARRIABLE ] ;

Example: Demonstration using rollback in SQL transaction

-----DEMONSTRATION OF USING ROLLBACK IN TRANSACTION SQL QUERY

CREATE TABLE TESTROLLBACK(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(20) )

GO

INSERT INTO TESTROLLBACK VALUES (1001,'JAMES GOSLING')

GO

BEGIN TRANSACTION T1; -------BEGIN TRANSACTION

GO

INSERT INTO TESTROLLBACK VALUES (1002,'RAHMAAN')

GO

SAVE TRANSACTION SAVEP1;  ------CREATE SAVE POINT  IN TRANSACTION

GO

INSERT INTO TESTROLLBACK VALUES (1003,'PETERE BABHBAD')

INSERT INTO TESTROLLBACK VALUES (1004,'HERMAINI')

UPDATE TESTROLLBACK SET EMPNAME = 'ARUN SINGH' WHERE EMPID =1003

GO

ROLLBACK TRANSACTION SAVEP1 ; ----------- ROLLBACK TRANSACTION TO SAVEP1 SAVEPOINT

GO

COMMIT TRANSACTION T1; --- COMMIT TRANSACTION T1 TO SAVE ALL WORK DONE

 

Transaction Control Language in SQL

Set transaction:  Set transaction command is used for Change transaction options like isolation level and what rollback segment to use. Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

The transaction isolation levels define the type of locks acquired on read operations. Shared locks acquired for read committed or repeatable read are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. For example, if a repeatable read transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.

Syntax: Demonstration of using set transaction isolation level in SQL server

----SYNTAX DEMONSTRATION OF CREATING SET TRANSACTION ISOLATION LEVEL IN SQL SERVER

SET TRANSACTION ISOLATION LEVEL

{

  READ UNCOMMITTED |

  READ COMMITTED |

  REPEATABLE READ |

  SNAPSHOT |

  SERIALIZABLE

 

}  ;

READ UNCOMMITTED: 

Read Uncommitted specifies that statements can read rows that have been modified by other transactions but not yet committed.

READ COMMITTED:

Read Committed specifies that statements cannot read data that has been modified but not committed by other transactions.

REPEATABLE READ:

Repeatable Read specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

SNAPSHOT:

Snapshot specifies that data read by any statement in a transaction will be the transactional consistent version of the data that existed at the start of the transaction.

SERIALIZABLE:

·         Statements cannot read data that has been modified but not yet committed by other transactions.

·         No other transactions can modify data that has been read by the current transaction until the current transaction completes.

·         Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Example:  Demonstration of using set transaction isolation level in SQL server

  ----DEMONSTRATION OF CREATING SET TRANSACTION ISOLATION LEVEL IN SQL SERVER

 

CREATE TABLE TESTSETTRANSACTION(STU_ID INT PRIMARY KEY, STU_NAME VARCHAR(30), STU_DESCRIPTION VARCHAR(100))

GO

INSERT INTO TESTSETTRANSACTION VALUES (100, 'HARRY WATSON' ,'HARRY DO NOT ATTEND 100% COLLEGE')

INSERT INTO TESTSETTRANSACTION VALUES (101, 'BRUCLI GOVINDA ' ,'BRUCLI IS A INTELLIGENT BOY ')

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;

GO

BEGIN TRAN T1;

GO

UPDATE TESTSETTRANSACTION SET STU_NAME = 'ARUN RAMALINGAM' WHERE STU_ID = 100

SELECT * FROM TESTSETTRANSACTION

GO

BEGIN TRAN T2 ;

GO

SELECT * FROM TESTSETTRANSACTION

GO

COMMIT TRAN T2;

GO

COMMIT TRAN T1;

GO

Transaction Control Language in SQL


Don't want to miss updates? Please click the below button!

Follow MindStick