articles

Home / DeveloperSection / Articles / Transaction Control Language:

Transaction Control Language:

Anonymous User22216 17-Jul-2011

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:

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:

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:

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:


Updated 07-Sep-2019
I am a content writter !

Leave Comment

Comments

Liked By