Transaction Control Language (TCL) commands are used to manage transactions in the database generated by DML statements. The statements are allowed to be grouped together into logical transactions.
TCL commands are as follows:
1. COMMIT
2. SAVEPOINT
3. ROLLBACK
4. SET TRANSACTION
COMMIT:
Any transaction can be permanently saved into the database using the commit command.
Syntax: COMMIT
ROLLBACK:
The ROLLBACK command returns the database to its most recently committed state. To leap to a savepoint in a transaction, it is also used with the savepoint command.
Syntax: ROLLBACK
SAVEPOINT:
To temporarily save a transaction so that you can roll back to it at any time, use the SAVEPOINT command.
Syntax: SAVEPOINT <savepoint_name>
SET TRANSACTION:
Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.
Syntax: SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
Example(1): COMMIT
BEGIN
INSERT INTO tbTest VALUES(1002, ‘BigData’, ‘Course-B1002’)
INSERT INTO tbTest VALUES(1003, ‘SQL’, ‘Course-B1003’)
COMMIT
The above-specified INSERT statements are committed, which means saved to disk.
Example(2): ROLLBACK
BEGIN
DELETE FROM tbTest WHERE CourseID = 1002;
INSERT INTO tbTest VALUES(1004, ‘Warehouse’, ‘Course-B1004’)
ROLLBACK
The INSERT statement and DELETE statement mentioned above are rolled back, which merely means that the statements are discarded. The alterations that resulted from such statements will be undone.
Example(3): SAVEPOINT
USE TestDB
GO
BEGIN TRANSACTION
INSERT INTO tbTest(SomeID, SomeValue) VALUES ( 10001, ‘Zafar Iqbal’)
INSERT INTO tbTest(SomeID, SomeValue) VALUES ( 10002, ‘Tahir Iqbal’)
— Let’s create a savepoint here.
SAVE TRANSACTION SavePoint1
INSERT INTO tbTest(SomeID, SomeValue) VALUES ( 10003, ‘Johnson’)
INSERT INTO tbTest(SomeID, SomeValue) VALUES ( 10004, ‘Peter’)
— Let’s create another savepoint here.
SAVE TRANSACTION SavePoint2
— Now, let’s roll back.
ROLLBACK TRANSACTION SavePoint1
With this, the third and fourth insert statements were discarded. The database state will return to the designated save point.
— Now, let’s issue the COMMIT
COMMIT
SELECT * FROM tbTest;
You’ll observe that the first two insert statements have successfully inserted the values. The next two insert statements’ values were gone.
Example(4): SET TRANSACTION
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tbTest;
Hope you find this article helpful.
Big Data and SQL 
One comment