--- title: "Transaction Control Language:" description: "Transaction Control Language (TCL) is playing an important role in SQL. Transaction control language is used for statements are used to manage the cha" author: "Anonymous User" published: 2011-07-17 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/568/transaction-control-language category: "database" tags: ["database"] reading_time: 7 minutes --- # 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 T1GO--- UPDATE NAME COLUMN OF USERLOGIN TABLE  UPDATE Userlogin SET Name ='James Baswar' WHERE ID = '101'GOCOMMIT 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))GOBEGIN TRANSACTION T1 ;GOINSERT INTO TESTTRANSACTION VALUES (1,'T1')----- @@TRANCOUNT RETURNS NUMBER OF ACTIVE TRANSACTION IN CURRENT CONNECTIONPRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GOBEGIN TRANSACTION T2;GOINSERT INTO TESTTRANSACTION VALUES (2,'T2')PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GOBEGIN TRANSACTION T3;GOINSERT INTO TESTTRANSACTION VALUES (3,'T3')PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GOCOMMIT TRANSACTION  T3;PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GOCOMMIT TRANSACTION T2;PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GOCOMMIT TRANSACTION T1;PRINT CAST(@@TRANCOUNT AS NVARCHAR(10))GO ``` ![Transaction Control Language in SQL](https://www.mindstick.com/mindstickarticle/f932f4b8-3262-4112-baab-48637f615b8a/images/e7ef22f1-d679-40a7-a7da-b82eaea791b4.png) **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))GOBEGIN TRANSACTION T1;GOINSERT INTO TESTSAVEPOINT VALUES (1001 , 'MOUSE')INSERT INTO TESTSAVEPOINT VALUES (1002 , 'KEYBOARD')UPDATE TESTSAVEPOINT SET PRODUCT_NAME = 'MONITOR' WHERE PRODUCT_ID = 1002  GOSAVE TRANSACTION SAVEP1; ---------- CREATING FIRST SAVEPOINT SAVEP1GOINSERT INTO TESTSAVEPOINT VALUES (1003, 'KEYBOARD')INSERT INTO TESTSAVEPOINT VALUES (1004, 'PRINTER')GOSAVE TRANSACTION SAVEP2;      ---------- CREATING FIRST SAVEPOINT SAVEP1GOINSERT 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](https://www.mindstick.com/mindstickarticle/f932f4b8-3262-4112-baab-48637f615b8a/images/d6cf5588-72b5-4af2-9c20-502e4ac211a3.png) **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 QUERYROLLBACK [TRAN | TRANSACTION ] [SAVEPOINT_NAME | SAVEPOINT_VARRIABLE ] ; ``` ##### Example: Demonstration using rollback in SQL transaction ``` -----DEMONSTRATION OF USING ROLLBACK IN TRANSACTION SQL QUERYCREATE TABLE TESTROLLBACK(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(20) )GOINSERT INTO TESTROLLBACK VALUES (1001,'JAMES GOSLING')GOBEGIN TRANSACTION T1; -------BEGIN TRANSACTION GOINSERT INTO TESTROLLBACK VALUES (1002,'RAHMAAN')GOSAVE TRANSACTION SAVEP1;  ------CREATE SAVE POINT  IN TRANSACTION GOINSERT INTO TESTROLLBACK VALUES (1003,'PETERE BABHBAD')INSERT INTO TESTROLLBACK VALUES (1004,'HERMAINI')UPDATE TESTROLLBACK SET EMPNAME = 'ARUN SINGH' WHERE EMPID =1003GOROLLBACK TRANSACTION SAVEP1 ; ----------- ROLLBACK TRANSACTION TO SAVEP1 SAVEPOINT GOCOMMIT TRANSACTION T1; --- COMMIT TRANSACTION T1 TO SAVE ALL WORK DONE ``` ![Transaction Control Language in SQL](https://www.mindstick.com/mindstickarticle/f932f4b8-3262-4112-baab-48637f615b8a/images/642f7437-7e12-48a9-a0ed-d6e3b2679d97.png) **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 SERVERSET 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))GOINSERT INTO TESTSETTRANSACTION VALUES (100, 'HARRY WATSON' ,'HARRY DO NOT ATTEND 100% COLLEGE')INSERT INTO TESTSETTRANSACTION VALUES (101, 'BRUCLI GOVINDA ' ,'BRUCLI IS A INTELLIGENT BOY ')GOSET TRANSACTION ISOLATION LEVEL READ COMMITTED ;GOBEGIN TRAN T1;GOUPDATE TESTSETTRANSACTION SET STU_NAME = 'ARUN RAMALINGAM' WHERE STU_ID = 100SELECT * FROM TESTSETTRANSACTIONGOBEGIN TRAN T2 ;GOSELECT * FROM TESTSETTRANSACTIONGOCOMMIT TRAN T2;GOCOMMIT TRAN T1;GO ``` \ ![Transaction Control Language in SQL](https://www.mindstick.com/mindstickarticle/f932f4b8-3262-4112-baab-48637f615b8a/images/aaea4d8e-5157-47d2-85bc-3b2042e1d179.png) --- Original Source: https://www.mindstick.com/articles/568/transaction-control-language Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.