How does a transaction work?

Any action performed on a database must be done with great caution. Deletes and updates records are the commands that require more care, but there are other situations that also require a lot of attention. Certain routines may have inserts in different tables where one is conditioned to another. In these cases, errors cannot occur when saving the records or manipulate them, that would compromise the integrity of the data in the database. In order to assist this process and ensure that the script can run without major problems is that there are transactions.

The concept of transaction in databases is very simple: a certain routine needs to run completely and without error to be complete. If an error occurs in the middle of the execution, all the commands that swirled above will be reversed, thus ensuring that only with the complete script running is that there is effectively a change in the database.

Transactions ensure that the information is not lost inside the bank, a very important resource, but that comes at a cost in performance. The entire transaction is charged on the processing of the database server about to let it unusable, if not implemented properly. An open transaction may make the server be very slow, and several can bring it down.

Next, we analyze a transaction use example:

DECLARE `_rollback` BOOL DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

START TRANSACTION;

INSERT INTO `tablea` (`date`) VALUES (NOW());

INSERT INTO `tableb` (`date`) VALUES (NOW());

INSERT INTO `tablec` (`date`) VALUES (NOW()); — FAIL

IF `_rollback` THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

In the example we have the basic structure of a transition, where:

• START TRANSACTION: Command that initiates the transaction;

• ROLLBACK: Command that rolls back the changes if an error is identified;

• COMMIT: Command that makes effective the transaction changes in the database.

The pattern structure of a transaction is composed by the commands START TRANSACTION and COMMIT; But the implementation of ROLLBACK command is needed to ensure that if an error occurs, the database will not suffer any change. This is a preventive measure that is worth being used, especially when handling several tables.

The transactions usually are used in procedures. So, to find them, it is important to understand the logic applied and ensure that the COMMIT and ROLLBACK commands are implemented correctly.

Check out more content on our blog!
Learn all about Scriptcase.