ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM test_trans;
+----+-------+
| id | value |
+----+-------+
| 3 | 3 |
| 5 | 4 |
| 6 | 5 |
+----+-------+
3 rows in set (0.00 sec)
Notice we begin by inserting several rows without transactions. In this case, the MySQL server is set
up to automatically commit these statements. This is called auto commit and can be controlled with the
autocommit option. Next, we start a transaction executing a number of statements and then roll back the
operation and check the results. Finally, you can see an example of how to commit.
One thing that may not be apparent with transaction processing is how the changes affect other
connections. For example, in the previous demonstration, what would happen if another connection were to
query the data before the transaction is committed? The connection would see the original, unaltered data
because it hasn’t been committed yet. Interestingly, the current connection would see the changes before
the commit because the changes are local to that session. You can see that in the SELECT in the middle of the
transaction.
Transactions are an excellent advanced technique to use in your solutions, especially once you develop
complex, multistep statements for your data. Also, transactions are a key concept used in more advanced
replication features such as global transaction identifiers.
Advanced Replication with Global Transaction Identifiers
Recall from Chapter 7 , you discovered how to set up replication using a mechanism that requires identifying
the binary log file and position from the master and then specifying that information on the slave with the
CHANGE MASTER TO statement. This is referred to as binary log file and position replication. However, there is
a newer, better way that improves performance and makes possible automatic failover and easier switchover.
The feature required is called replication with global transaction identifiers (GTIDs) or GTID-based
replication.
GTIDs enable servers to assign a unique identifier to each set or group of events, thereby making it
possible to know which events have been applied on each slave. That is, when GTIDs are enabled, the
replication component of the MySQL server will inject a special event into the binary log that contains a
globally unique identifier that is unique to each server and a sequence number at the start of a transaction.
In this way, MySQL replication can detect what events are associated with each transaction and even
from which server transactions were sent. Best of all, the replication protocol can now determine which
transactions are missing on a slave so that when a slave connects, only those transactions that are not
already applied to the slave get sent across.
Now let’s see how to set up a master and several slaves using GTIDs. If your servers already have data
on them, you should synchronize the data on all servers before turning on GTIDs. However, before we turn
on GTIDs, you should create a user on the master that the slaves can use to connect. This user must have