DATABASE TRANSACTION

DATABASE TRANSACTION

A database transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database

PROPERTIES OF TRANSACTION

In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction: atomicity, consistency, isolation, and durability. All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are

ATOMICITY

Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed.

CONSISTENCY

Data is in a consistent state when a transaction starts and when it ends. For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction

ISOLACTION

Among these four properties (Atomicity, Consistency, Isolation and Durability) Isolation determines how transaction integrity is visible to other users and systems

DURABILIY

A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity

COMMIT AND ROLL BACK

The COMMIT statement commits the database changes that were made during the current transaction, making the changes permanent. ... The ROLLBACK statement backs out, or cancels, the database changes that are made by the current transaction and restores changed data to the state before the transaction began

AUTO COMMIT

By default, JDBC uses an operation mode called auto-commit. This means that every update to the database is immediately made permanent. Any situation where a logical unit of work requires more than one update to the database cannot be done safely in auto-commit mode

GENERIC EXAMPLE ON TRANSACTION

In a database management system, a transaction is a single unit of logic or work, sometimes made up of multiple operations. ... One example is a transfer from one bank account to another: the complete transaction requires subtracting the amount to be transferred from one account and adding that same amount to the other

TRANSACTION SAFE TABLES TYPE IN MYSQL

To use transactions in MySQL, you must use a transaction-safe table type. The default MySQL table type, MyISAM, does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in the open source MySQL, version 3.23

PERFORMING A BASIC TRANSACTION

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency

THE SART TRANSACTION SATMENT

1)THE START TRANSACTION REQUIRES NO CLAUSES OR OPTIONS.

2)START TRANSACTION STATMENT NOTIFYS MYSQL THAT THE STATMENTS THAT FOLLOW SHOULD BE TREATED AS A UNINT UNTIL,THE TRANSCATION ENDS

THE COMMIT STATMENT

The SQL COMMIT TRANSACTION statement ends a transaction by commiting a transaction that has been started

ROLLBACK STATMENT

ROLLBACK in SQL is a transactional control language which is used to undo the transactions that have not been saved in database. The command is only be used to undo changes since the last COMMIT.

SAVEPOINT STATMENT

The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction

ROLLBACK TO SAVE POINT

A simple rollback or commit erases all savepoints. When you roll back to a savepoint, any savepoints marked after that savepoint are erased. The savepoint to which you roll back remains. You can reuse savepoint names within a transaction

STATMENTS THAT AUTOMACTICALLY COMMIT TRANSCATIONS

There are four Auto-commit commands that exist in SQL, they are:

SET AUTOCOMMIT ON – By executing this particular command, the auto-commit status turned to be ON, if it is OFF initially. ...

SET AUTOCOMMIT OFF – This instruction is just the reverse of the first one. ...

SET AUTOCOMMIT INT_VALUE – ...

SHOW AUTOCOMMIT –

IN THIS TYPE OF QUESTION YOU READ THE QUESTION AND CLICK ON THE ANSWER GIVEN IN THREE BLOCKS BUT ONLY ONE OF THEM IS CORRECT

THIS IS MUCH SIMMILAR LIKE THE PREVIOUS ONE HERE MORE THAN ONE ANS WILL BE CORRECT SO TO KNOW IT YOU SELECT YOUR ANSWER AND SEE IF A TICK MARK COMES ON THAT ANS MEANS IT IS A CHECK BOX QUESTION SO YOU HAVE TO SLECT ONE MORE ANSWER AND A OPTION YOU WILL SEE ON YOUR LEFT BOTTOM SIDE OF YOUR SCREEEN AND YOU WILL ABLE TO ANS IT

IN THIS TYPE OF QUESTION YOU ALL HAVE TO TYPE YOU ANSWER IN THE BLOCK GIVEN UNDER THE QUESTION AND A TICK OPTION WILL BE SHOWN TO YOU AFTER YOU GAVE THE ANSWER .