Run Mysql Query Again if It Fails No Transactions

What is a transaction?

If y'all are request this
question, you lot are probably used to website databases, where about oftentimes it does
not matter in which order you run transactions, and if one query fails, information technology has
no impact on others. If you lot are updating some sort of content, oftentimes you will
not care when the update is performed, equally long as the reads are being taken
intendance of quickly. Similarly, if an update fails, the reads can still acquit on
reading the old data in the meantime. However, at that place are times when it is
vitally important in which gild queries run, and that all queries in a grouping
run, or none at all. The archetype example is from the cyberbanking environment. An
amount of coin is taken from one person's account, and put into another, for
example as follows, a 500-unit transaction:

UPDATE account1 SET balance=residuum-500; UPDATE account1 Ready residue=balance+500;        

Both queries must run, or
neither must run. You lot cannot have the coin being transferred out of 1
person's account, and so 'disappearing' if for some reason the second query
fails. Both these queries form one transaction. A transaction is simply
a number of individual queries that are grouped together.

A modest dose of ACID

For a long time, when
MySQL did not support transaction, its critics complained that information technology was not Acrid
compliant. What they meant, is that MySQL did non comply with the four weather
to which transactions need to adhere in order to ensure data integrity. These
four conditions are:

• Atomicity: An
  atom is meant to be the smallest particle, or something that cannot exist divided.
  Atomicity applies this principle to database transactions. The queries that
  make up the transaction must either all exist carried out, or none at all (as with
  our cyberbanking example, to a higher place).
• Consistency:
  This refers to the rules of the information. For example, an article body may have to
  have an associated article heading. During the transaction, this rule may be
  cleaved, but this state of affairs should never be visible from outside of the
  transaction.
• Isolation:
  Simply put, data being used for one transaction cannot be used by another
  transaction until the first transaction is consummate. Take this example below,
  where an business relationship balance starts at 900. There is a single deposit of 100, and a
  withdrawal of 100, so the balance at the end should remain the same.

                
                Connection 1: SELECT balance FROM account1;
                Connectedness 2: SELECT balance FROM account1;
                Connection 1: UPDATE account1 Set residual = 900+100;
                Connection two: UPDATE account1 SET balance = 900-100;            

  The rest is now 800, and then nosotros take lost 100. These two
  transactions should accept been isolated, and the event supplied to Connexion 2
  merely when the transaction from Connexion one was complete.

• Durability:
  Once a transaction has completed, its furnishings should remain, and not be reversible.

Down to piece of work: InnoDB Transactions

Transactions are wrapped
in BEGIN and COMMIT statements. Let's create a sample InnoDB
table, and run into how transactions work:

mysql> CREATE TABLE t (f INT) Type=InnoDB;

Now let's begin a
transaction, and insert a tape:

          
          mysql> BEGIN;
          Query OK, 0 rows affected (0.00 sec)          
mysql> INSERT INTO t(f) VALUES (i);
            Query OK, 1 row afflicted (0.01 sec)
          
mysql> SELECT * FROM t;
            +——+
            | f    |
            +——+
            |    ane |
            +——+
            1 row in set (0.02 sec)
          
mysql> ROLLBACK;
            Query OK, 0 rows afflicted (0.01 sec)
          
mysql> SELECT * FROM t;
            Empty set (0.00 sec)
        

Without a COMMIT
statement, the insert was non permanent, and was reversed with the ROLLBACK.
Note that the added record was visible during the transaction from the same connectedness
that added information technology.

Consistent reads

Let'due south try
looking from a unlike connection. For this exercise, open two connections to
the database.

Connectedness one:

          
          mysql> Brainstorm;
          Query OK, 0 rows affected (0.00 sec)          
mysql> INSERT INTO t (f) VALUES (1);
            Query OK, i row affected (0.00 sec)
          
mysql> SELECT * FROM t;
            +——+
            | f    |
            +——+
            |    1 |
            +——+
            ane row in ready (0.00 sec)
        

Connection 2:

mysql> SELECT * FROM t; Empty set (0.02 sec)        

The
important point is that running the same query from different connections (one
within the middle of a transaction, the other from without) produces different
results. Now, commit the transaction from the first connection, and run the
query over again from connection 2.

Connection
1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

Connectedness
2:

mysql> SELECT * FROM t; +------+ | f    | +------+ |    1 | +------+ 1 row in prepare (0.00 sec)        

This behavior is chosen consistent
reading. Whatever select returns a event upward until the most recently completed
transaction, with the exception of the connectedness doing the updating, as we saw
above. Past default, MySQL InnoDB tables perform consequent reads.

Automatic Commits

MySQL too automatically
commits statements that are not function of a transaction. The results of any
UPDATE or INSERT argument not preceded with a BEGIN will immediately be
visible to all connections. You tin change this beliefs, as follows:

mysql> Gear up AUTOCOMMIT=0; Query OK, 0 rows afflicted (0.00 sec)

Now, note what happens,
fifty-fifty if we practise non specifically start a transaction with Brainstorm.

Connection i:

mysql> INSERT INTO t (f) VALUES (2); Query OK, 1 row affected (0.00 sec)  mysql> SELECT * FROM t; +------+ | f    | +------+ |    1 | |    2 | +------+ 2 rows in fix (0.00 sec)        

Connection 2:

mysql> SELECT * FROM t; +------+ | f    | +------+ |    1 | +------+ 1 row in prepare (0.00 sec)        

Commit the transaction from
the first connexion, then reset AUTOCOMMIT to one, and echo the
exercise:

Connectedness 1:

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)  mysql> INSERT INTO t (f) VALUES (3); Query OK, ane row affected (0.00 sec)        

Connection two:

mysql> SELECT * FROM t; +------+ | f    | +------+ |    1 | |    2 | |    iii | +------+ three rows in set (0.00 sec)        

This time the transaction
is committed immediately, and is visible from another connexion, even without
a specific COMMIT statement.

Ian Gilfillan

Ian Gilfillan lives in Cape Town, Southward Africa. He is the author of the book 'Mastering MySQL iv', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and notwithstanding has a soft spot for Perl. He developed South Africa's outset online grocery store, and has developed and taught internet development and other technical courses for diverse institutions. He has majors in Programming and Data Systems, too equally English language and Philosophy. For 5 years he was Lead Programmer and IT Manager for Contained Online, South Africa'south premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book volition be more in the style of William Blake and Allen Ginsberg.

conroyproadite.blogspot.com

Source: https://www.databasejournal.com/mysql/transactions-in-mysql/

Share this post