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:




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

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

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

IF `_rollback` THEN





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.

By ,

June 18, 2015


You might also like…

How to Create and Read QR Codes and Barcodes

“Point your cell phone camera to read the QR code.” This phrase has been increasingly recurring...

Why is Low-Code essential for any business?

Low-Code is the IT term that refers to the process of using little code to develop software and app...

“The Preventus Medical Center system has more than 222,000 patients and 115 professionals.” (DRey Solutions)

Check out how DRey Solutions uses Scriptcase to streamline and synthesize the process of maintenanc...

You might also like…

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 Learn more about our Privacy Police.