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.

transaction.fw

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.

You might also like…

PROCESAMIENTO AJAX EN SCRIPTCASE

Ajax en los formularios de Scriptcase puede usarse para recargar un campo de tipo de select basado e...

IMPORTAR IMAGENES DE LA BASE DE DATOS Y UTILIZAR CLASES DE BOOSTRAP

Como muchas personas ya saben, Bootstrap es una biblioteca de front-end libre y de código abierto p...

TENDENCIAS DE DESARROLLO WEB PARA 2017

Usted verá en este post tendencias para 2017 en Diseño Web, en el medio digital y en el desarrollo...

Comment this post

Get new posts, resources, offers and more each week.

We will use the information you provide to update you about our Newsletter and Special Offers. You can unsubscribe any time you want by clinck in a link in the footer of any email you receive from us, or by contacting us at sales@scriptcase.net. Learn more about our Privacy Police.