Definition – What is SQL Transaction?
- SQL Transaction is a single unit of work applied on a Database.(relational database like DB2, Oracle, MySQL, Microsoft SQL Server, and so on)
- Its a sequence of operations performed on database in an logical order.
- The SQL statements of an transaction will be either commited or rolled back.
- Transaction comprises with two results, they are either Success or Failure.
These transactions can be Oracle transaction, SQL server transaction, DB2 transaction and so on.
SQL Transaction Properties
Typically transactions in SQL have four standard properties and they are abbreviated as ‘ACID’.
Transactions in SQL are aborted at point of failure, by this way it ensure the operations of work units were completed successfully.
Upon a successful commited transaction, it will ensure the database state modifications is done.
Transactions will operate independently and transparently.
In case of system failure, it ensures the result of commited Transaction is persist across the application.
Following are the commands used to control the transactions.
This command will save the changes.
This command will roll back(undo or revert) the changes.
This command creates save points for the groups of transactions that has to be ROLLBACK.
SET TRANSACTION :
Sets a name on a transaction.
SET TRANSACTION [ READ ONLY | READ WRITE];
A transaction in database begins when an initial or first exectuble SQL statement is encountered in the server.
An executable SQL statement is a SQL statement that triggers calls to an instance, DML and DDL query statements.
When a start transaction begins, respective database allocates the transaction to an available undo tablespace to track the rollback entries for the new transaction.
A transaction will be completed when any one of the following occurs:
- User triggers a COMMIT command or ROLLBACK statement command without a SAVEPOINT clause.
- User runs a DDL statement such as CREATE, DROP, RENAME, or ALTER.
- User disconnects from Database connection, the current transaction is committed.
- User process terminates abnormally, The current transaction is rolled back.
Also note that transactions were not used on SELECT statement or SELECT SQL script generally.
Typically transactions were effectively used in the stored procedure. It is best practice to include try and catch block to allow performing a ROLLBACK TRANSACTION statement in case some error occurs in the stored procedure.
Committing a transaction is nothing but making the changes performed by the SQL statements within the transaction as permanent.
Before a transaction that modifies data through commit command, the following has been occurred:
- Database has generated the undo information, the undo information contains the legacy or old data values changed by the SQL statements of the transaction.
- Database has generated redo log files entries in the redo log buffer of the SGA.
- The redo log files record contains the change to the data and rollback block, these changes may go to disk before a transaction is committed.
- The changes have been made to the SGA database buffers, these changes may go to disk before a transaction is committed.
When a transaction commit command execute, the following occurs:
- The internal transaction table for the respective undo tablespace records that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.
- The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file. It also writes the transaction’s SCN to the redo log file
- Database releases locks held on rows and tables.
- Database marks the transaction complete.
Savepoints In Transactions
we can declare intermediate points called savepoints within the context of a transaction. Savepoints separate a lengthy transaction into smaller parts.
Savepoints are equally useful in application programs. If a specific procedure contains numerous functions, then we can create a savepoint before each function starts.
Then, if a function fails, it will be easy to return the data to its actual state before the function began and re-run the function with revised parameters.
These save points especially useful when we are dealing with recovery model or backup actions for avoiding data loss.
After a commit rollback to a specific savepoint, Database releases the data locks obtained by rolled back statements. So transactions waiting for previously locked resources can proceed and stored back.
When a transaction is rolled back to a specific
- Database rolls back only the statements that were executed after the savepoint.
- Database maintains the specified savepoint, but all savepoints that were set after the specified one are lost.
- Database releases all table and row locks acquired since that savepoint but holds all data locks acquired earlier to the savepoint.
What is SQL Transaction Log?
The SQL transaction log is a file which contains the logs that were generated during the logging process for the respective transaction processing occurred in database application.
SQL transaction logs are sequential in nature and can be split into chunks called virtual log file.
The SQL transaction log supports the below procedure;
- Recovering Incomplete Transactions.
- Rollback SQL Transaction.
- High Availability.
- Rolling a restored DB
The respective relational Database will have its corresponding transaction processing log like SQL server database captures the transactions in SQL server using SQL server transaction log, Oracle database have oracle transaction log and so on.
Transaction log backup is a procedure for database backup of transactions occurred since the last transaction log backup was taken. These database backup can be done in Full and Incremental.
SQL Transaction Concurrency
Multiple transactions which were running together may access the same database rows during overlapping time period. Such simultaneous access events called Collisions, may result in inconsistencies or error which causes transaction concurrency.
The more overlapping that is possible, the greater the concurrency.
Proper handling of collisions requires some work on the part of the application programmer. It is possible to leave the whole concurrency matter in the hands of the DBMS, however that would almost lead to performance which everyone would call unacceptable.
So application programmer have key role to play while handling of collisions properly by using available SQL options which can increase concurrency. This will eventually help the DBMS along with a wide variety of application plans and connection.
Implementation and Execution of Locking, Timestamp mechanisms is the best way to avoid or delete majority of transaction concurrency problems.
Read more for SQL wiki @ SQL Guide