MySQL - START TRANSACTION Statement

In general, transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A database transaction is the propagation of one or more changes as a single action on the database.

For example, if you consider the transfer of certain amount from one account to other as a single transaction. This basically contains three steps −

  • Reading the money to be transferred.
  • Verifying whether the amount is available in the sender’s account.
  • Crediting money from the sender account.
  • Debiting money to the receiver account.

For the amount to be transferred all the above operations should be successfully completed. To make sure this happens you can use Transactions and treat all these steps as a single action.

MYSQL provides supports for transactions using the SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK statements.

The START TRANSACTION statement

The START TRANSACTION statement of MySQL is used to start a new transaction.

Syntax

Following is the syntax of the START TRANSACTION statement −

START TRANSACTION [transaction_characteristic
[, transaction_characteristic]...]

Example

Assume we have created a table named emp and populated it with 3 records as −

CREATE TABLE accounts(
   Name VARCHAR(255), 
   DOB DATE, 
   Brach VARCHAR(255), 
   balance INT
);

Now, let us insert some records into the accounts table −

INSERT INTO accounts VALUES 
('Amit', DATE('1970-01-08'), 'Hyderabad', 2554),
('Sumith', DATE('1990-11-02'), 'Vishakhapatnam', 5589),
('Sudha', DATE('1980-11-06'), 'Vijayawada', 5587);

You can verify the contents of a table using SELECT statement as −

SELECT * FROM accounts;

Output

Following is the output of the above query −

Name DOB Branch balance
Amit 1970-01-08 Hyderabad 2554
Sumith 1990-11-02 Vishakhapatnam 5589
Sudha 1980-11-06 Vijayawada 5587

In the below set of MySQL queries we are starting a transaction and transferring 1500/- from the account of Amit to Sudha.

START TRANSACTION;

UPDATE accounts SET balance= balance-1500 WHERE Name='Amit';
UPDATE accounts SET balance= balance+1500 WHERE Name='sudha';

COMMIT;

If you verify the contents of the accounts table again, you can observe the updated balances.

SELECT * FROM accounts;

Output

The above program produces the following output −

Name DOB Branch balance
Amit 1970-01-08 Hyderabad 1054
Sumith 1990-11-02 Vishakhapatnam 5589
Sudha 1980-11-06 Vijayawada 7087
Advertisements