Rechercher dans le manuel MySQL
17.4.1.34 Replication and Transactions
Mixing transactional and nontransactional statements within the same transaction. In general, you should avoid transactions that update both transactional and nontransactional tables in a replication environment. You should also avoid using any statement that accesses both transactional (or temporary) and nontransactional tables and writes to any of them.
The server uses these rules for binary logging:
If the initial statements in a transaction are nontransactional, they are written to the binary log immediately. The remaining statements in the transaction are cached and not written to the binary log until the transaction is committed. (If the transaction is rolled back, the cached statements are written to the binary log only if they make nontransactional changes that cannot be rolled back. Otherwise, they are discarded.)
For statement-based logging, logging of nontransactional statements is affected by the
binlog_direct_non_transactional_updatessystem variable. When this variable isOFF(the default), logging is as just described. When this variable isON, logging occurs immediately for nontransactional statements occurring anywhere in the transaction (not just initial nontransactional statements). Other statements are kept in the transaction cache and logged when the transaction commits.binlog_direct_non_transactional_updateshas no effect for row-format or mixed-format binary logging.
Transactional, nontransactional, and mixed statements. To apply those rules, the server considers a statement nontransactional if it changes only nontransactional tables, and transactional if it changes only transactional tables. A statement that references both nontransactional and transactional tables and updates any of the tables involved is considered a “mixed” statement. Mixed statements, like transactional statements, are cached and logged when the transaction commits.
A mixed statement that updates a transactional table is considered unsafe if the statement also performs either of the following actions:
Updates or reads a temporary table
Reads a nontransactional table and the transaction isolation level is less than REPEATABLE_READ
A mixed statement following the update of a transactional table within a transaction is considered unsafe if it performs either of the following actions:
Updates any table and reads from any temporary table
Updates a nontransactional table and
binlog_direct_non_transactional_updatesis OFF
For more information, see Section 17.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”.
A mixed statement is unrelated to mixed binary logging format.
In situations where transactions mix updates to transactional
and nontransactional tables, the order of statements in the
binary log is correct, and all needed statements are written to
the binary log even in case of a
ROLLBACK.
However, when a second connection updates the nontransactional
table before the first connection transaction is complete,
statements can be logged out of order because the second
connection update is written immediately after it is performed,
regardless of the state of the transaction being performed by
the first connection.
Using different storage engines on master and slave.
It is possible to replicate transactional tables on the master
using nontransactional tables on the slave. For example, you
can replicate an InnoDB master table as a
MyISAM slave table. However, if you do
this, there are problems if the slave is stopped in the middle
of a BEGIN
... COMMIT block because the
slave restarts at the beginning of the
BEGIN block.
It is also safe to replicate transactions from
MyISAM tables on the master to
transactional tables—such as tables that use the
InnoDB storage engine—on the
slave. In such cases, an
AUTOCOMMIT=1
statement issued on the master is replicated, thus enforcing
AUTOCOMMIT mode on the slave.
When the storage engine type of the slave is nontransactional, transactions on the master that mix updates of transactional and nontransactional tables should be avoided because they can cause inconsistency of the data between the master transactional table and the slave nontransactional table. That is, such transactions can lead to master storage engine-specific behavior with the possible effect of replication going out of synchrony. MySQL does not issue a warning about this, so extra care should be taken when replicating transactional tables from the master to nontransactional tables on the slaves.
Changing the binary logging format within transactions.
The binlog_format and
binlog_checksum system
variables are read-only as long as a transaction is in
progress.
Every transaction (including
autocommit transactions) is
recorded in the binary log as though it starts with a
BEGIN
statement, and ends with either a
COMMIT or a
ROLLBACK
statement. This is even true for statements affecting tables
that use a nontransactional storage engine (such as
MyISAM).
For restrictions that apply specifically to XA transactions, see Section C.6, “Restrictions on XA Transactions”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-features-transactions.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.
