Define Transaction in DBMS | SQL Tutorial and Query Example

Text copied!

Define Transaction in DBMS

July 9, 2023, 10:57 p.m. under SQL
    • Here's a simplified syntax for SQL Server transaction :
    BEGIN TRANSACTION :

    BEGIN TRANSACTION marks the start of a transaction. You can define it as "BEGIN TRANSACTION," "BEGIN TRAN," or "BEGIN TRANSACTION [Transaction_Name]" to initiate a transaction.

    ROLLBACK TRANSACTION :

    ROLLBACK TRANSACTION undoes the changes made in the transaction if an error occurs. You can define it as "ROLLBACK TRANSACTION," "ROLLBACK TRAN," or "ROLLBACK TRANSACTION [Transaction_Name]" to rollback a transaction.

    BEGIN TRANSACTION; -- Start the transaction


-- SQL statements to perform within the transaction


ROLLBACK TRANSACTION; -- If any error occurs, rollback the transaction

COMMIT TRANSACTION; -- If everything goes well, commit the transaction
    COMMIT TRANSACTION :

    COMMIT TRANSACTION saves the changes made in the transaction to the database if everything was successful. You can define it as "COMMIT TRANSACTION," "COMMIT TRAN," or "COMMIT TRANSACTION [Transaction_Name]" to rollback a transaction.

    Here's a simple example of a transaction in SQL Server :-
    This is an example where all the queries within the transaction run without any errors :

    1. Let's consider a table named '[Product]' containing the following values :

    This is an example where one of the queries within the transaction encounters an error :

    1. Let's consider a table named '[Product]' containing the following values :

    In the next chapter, we will explore the ACID properties of transaction in DBMS.
    Define Transaction in DBMS 
    BEGIN TRANSACTION ABC

INSERT INTO [dbo].[Product] (
	    [Product_Id], [Name], [GroupName], [ModifiedDate] )
        VALUES 
		(2, 'Soap', 'Group-B', '2023-01-01 00:00:00.000');

UPDATE [dbo].[Product]
	SET [GroupName] = 'Sales'
	WHERE [Product_Id] = 1;

ROLLBACK TRANSACTION ABC
    Define Transaction in DBMS Define Transaction in DBMS 
    BEGIN TRANSACTION ABC

INSERT INTO [dbo].[Product] (
	    [Product_Id], [Name], [GroupName], [ModifiedDate] )
        VALUES 
		(2, 'Soap', 'Group-B', '2023-01-01 00:00:00.000');

UPDATE [dbo].[Product]
	SET [GroupName] = 'Sales'
	WHERE [Product_Id] = 1;

COMMIT TRANSACTION ABC
    Define Transaction in DBMS Define Transaction in DBMS Define Transaction in DBMS 
    BEGIN TRANSACTION ABC

INSERT INTO [dbo].[Product] (
	    [Product_Id], [Name], [GroupName], [ModifiedDate] )
        VALUES 
		(2, 'Soap', 'Group-B', '2023-01-01 00:00:00.000');

UPDATE [dbo].[Product]
	SET [GroupName] = 'Sales'
	WHERE [Product_Id] = 'A';

ROLLBACK TRANSACTION ABC
    Define Transaction in DBMS Define Transaction in DBMS 
    BEGIN TRANSACTION ABC

INSERT INTO [dbo].[Product] (
	    [Product_Id], [Name], [GroupName], [ModifiedDate] )
        VALUES 
		(2, 'Soap', 'Group-B', '2023-01-01 00:00:00.000');

UPDATE [dbo].[Product]
	SET [GroupName] = 'Sales'
	WHERE [Product_Id] = 'A';

COMMIT TRANSACTION ABC
    Define Transaction in DBMS Define Transaction in DBMS

    Frequently Asked Questions :

    A transaction in a database management system (DBMS) is a defined sequence of operations that must be completed as a whole, ensuring atomicity, consistency, isolation, and durability (ACID properties). Define Transaction in DBMS as a structured set of actions executed on a database to maintain data integrity.
    Transaction in DBMS refers to a unit of work that includes one or more operations. For instance, transferring funds from one bank account to another constitutes a transaction in DBMS.
    In DBMS, a transaction is a logical unit of work that is performed against a database. Transactions in a database management system (DBMS) must maintain ACID properties: Atomicity, Consistency, Isolation, and Durability. These properties of DBMS ensure that transaction are reliable and maintain data integrity.
    Transaction in DBMS is a set of operations performed as a single logical unit of work. For example, imagine buying a book online – it involves searching for the book, adding it to your cart, and then making the payment, all of which form a transaction.
    « Previous
    Next »
    RELATED :