Transactions

The transactions They are a sequence of instructions to query or update

The SQL standard specifies that a transaction implicitly begins when a SQL statement is executed

At the end of the instruction must run one of the following SQL statements:

• COMMIT WORK

  Validates updates made: so far and done so to become permanent in the database

  A complete turn, automatically starts the next transaction

  CREATE TABLE acount(
  number_acount char(10),
  name_branch char(15),
  balance numeric(12, 2),
  primary key(number_acount) );
  
  INSERT INTO acount
  VALUES ('C-9732', 'Navacerrada', 1200);
  INSERT INTO acount
  VALUES ('C-9733', 'Navacerrada', 1100);
  INSERT INTO acount
  VALUES ('C-9734', 'Navacerrada', 1000);
  INSERT INTO acount
  VALUES ('C-9735', 'Navacerrada', 900);
  INSERT INTO acount
  VALUES ('C-9736', 'Navacerrada', 800);
  INSERT INTO acount
  VALUES ('C-9737', 'Navacerrada', 700);
  INSERT INTO acount
  VALUES ('C-9738', 'Navacerrada', 600);
  COMMIT;

  In the example we have created a relationship called acount and we populated with 7 tuples

  The query or update instructions are separated from each other by the symbol ; indicating that SQL is a new instruction

  At the end we have implemented COMMIT so that the changes are saved to the database

• ROLLBACK WORK

  Causes the current transaction to be aborted, that is, it cancels all operations performed on the database

  The database returns to the initial state, before the first instruction of the transaction

  CREATE TABLE acount(
  number_acount char(10),
  name_branch char(15),
  balance numeric(12, 2),
  primary key(number_acount) );
  
  INSERT INTO acount
  VALUES ('C-9732', 'Navacerrada', 1200);
  INSERT INTO acount
  VALUES ('C-9733', 'Navacerrada', 1100);
  INSERT INTO acount
  VALUES ('C-9734', 'Navacerrada', 1000);
  INSERT INTO acount
  VALUES ('C-9735', 'Navacerrada', 900);
  INSERT INTO acount
  VALUES ('C-9736', 'Navacerrada', 800);
  INSERT INTO acount
  VALUES ('C-9737', 'Navacerrada', 700);
  INSERT INTO acount
  VALUES ('C-9738', 'Navacerrada', 600);
  ROLLBACK;

  In the example we have created a relationship called acount and we populated with 7 tuples

  The query or update instructions are separated from each other by the symbol ; indicating that SQL is a new instruction

  At the end we have implemented ROLLBACK so that the changes will not be kept in the database

The key word WORK is optional in both directions, so we could skip in the examples

Use ROLLBACK It is useful when an error is detected and it is necessary to a stable state of the database

Once it executed COMMIT, it is not possible to make a ROLLBACK, as data changes become permanent

Therefore, when the database systems detect a failure (either a voltage drop, a drop of the system, a bad connection in the network), internally perform one ROLLBACK it is denied the use of the COMMIT, to prevent the loss of information

In the special case of the system crash, the ROLLBACK will be done when the system restarts

Starting with the SQL:1999 standard, the contents of a transaction can be enclosed in the following format:

\text{BEGIN}\\ S_1;\\ S_2;\\ \cdots;\\ S_n;\\ \text{END}

Where s_i it's a consultation instruction or an update

BEGIN
    CREATE TABLE acount(
    number_acount char(10),
    name_branch char(15),
    balance numeric(12, 2),
    primary key(number_acount) );

    INSERT INTO acount
    VALUES ('C-9732', 'Navacerrada', 1200);
    INSERT INTO acount
    VALUES ('C-9733', 'Navacerrada', 1100);
    INSERT INTO acount
    VALUES ('C-9734', 'Navacerrada', 1000);
    INSERT INTO acount
    VALUES ('C-9735', 'Navacerrada', 900);
    INSERT INTO acount
    VALUES ('C-9736', 'Navacerrada', 800);
    INSERT INTO acount
    VALUES ('C-9737', 'Navacerrada', 700);
    INSERT INTO acount
    VALUES ('C-9738', 'Navacerrada', 600);
END
COMMIT;

The example is similar to that shown above for COMMIT

However, on this occasion we have locked up the transaction in the format of the SQL:1999 standard

This way we can tell where different transactions start and end without having to look for instructions COMMIT or ROLLBACK