MySQL Advanced Features-transaction processing

To use transactions in MySQL, you first need to create a table that uses a transaction table type, such as BDB = Berkeley db or InnoDB.

CREATE TABLE account (
account_id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
balance DOUBLE
) TYPE = InnoDB;

To use transactions on a transaction table, you must first turn off autocommit:

SET autocommit = 0;

Transactions begin with the BEGIN command:

BEGIN;

The MySQL client is now in the context of server-related things. Any changes made to the transaction table will not be permanent until they are submitted.

UPDATE account SET balance = 50.25 WHERE account_id = 1;

UPDATE account SET balance = 100.25 WHERE account_id = 2;

After making all the changes, use the commit command to complete the transaction:

COMMIT;

Of course, the real advantage of transaction processing is that it manifests itself in the execution of an error in the second statement, which can be rolled back if the entire transaction is terminated before committing:

ROLLBACK;

Here is another example of a direct mathematical operation via MySQL:

SELECT @first := balance FROM account WHERE account_id = 1;
SELECT @second := balance FROM account WHERE account_id = 2;
UPDATE account SET balance = @first - 25.00 WHERE account_id = 1;
UPDATE account SET balance = @second + 25.00 WHERE account_id = 2;

In addition to the commit command, the following command automatically ends the current transaction:

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP TABLE
LOCK TABLES
RENAME TABLE
TRUNCATE
UNLOCK TABLES