Sorry for pm ! ----- Am 1. Apr 2016 um 17:45 schrieb shawn l.green shawn.l.gr...@oracle.com:
>>> You would be better served by first converting your MyISAM tables to >>> InnoDB to stop mixing storage engine behaviors (transactional and >>> non-transactional) within the scope of a single transaction. But if you >>> cannot convert them, using MIXED will be a good compromise. >> >> Is the mix of MyISAM and InnoDB a problem with Row-Based-Logging or >> with Statement-Based-Logging or with both ? > Both. Aah ! In the beginning i thought it's just a problem for RBL. >>> Look at this sequence and think what would happen without that "stronger >>> locking" you mentioned earlier. >>> >>> (connection 1) >>> begin transaction >>> INSERT myisam_table SELECT ... FROM InnoDB_table WHERE ... >>> (connection 2) >>> DELETE myisam_table WHERE ... (this removes one of the rows that >>> connection 1 just added) >> (end of connection 2) >>> (connection 1) >>> COMMIT >> I don't understand the example: >> Does "begin transaction" and "COMMIT" have any influence on the insert ? >> From what i understand a myisam table does not support transactions, >> so it should not care about "begin transaction" and "commit". >> So the insert should be done immediately. The select on the InnoDB also >> should not wait, because it's applied without "LOCK IN SHARE MODE". >> So x lines are added immediately. This is done on the master, written in the >> log >> and then replicated to the slave, which also adds x lines. >> Then connection 2 deletes 8 rows, one is from the previous insert. >> First on the master and then on the slave. >> I assume that the connections are established in the order they appear here >> (connection 2 is established after the insert in connection 1). >> So on both 8 rows are deleted. >> >> > > > You said, "This is done on the master, written in the log and then > replicated to the slave, " > > The INSERT would not appear in the Binary log until after session 1 > commits. So the INSERT take care about the transaction (begin transaction ... COMMIT) although it's a MyISAM table ? Because i read MyISAM does not care about it: http://stackoverflow.com/questions/8036005/myisam-engine-transaction-support >> Does commit means "write now to the binlog" ? >> > > Effectively, it does (for InnoDB-based transactions). InnoDB first > writes the entire transaction to the Binary Log (it was sitting in the > Binlog cache up until this point) then it pumps the necessary data into > the REDO log (for disaster recovery). And when in that temporal sequence is the data written to the tablespace ? > At that point the transaction is > considered "committed". In the case of a rollback, there is nothing to > log in either location, no permanent changes were made to the data. > However if the transaction that rolled back contained statements that > changed MyISAM tables, then the entire transaction (all of the work it > did) needs to be written into the Binary Log and REDO log just to have > the very last command be "ROLLBACK". What that will do is create the > same sequence of data changes on the slave that happened on the master. So when transaction is rollbacked, the inserted data in the MyISAM table remains ? Thaks again. Bernd P.S. i tried several times to rename the subject into something like "Replication - was "need help from the list admin"", but this mail is always bounced back because it is recognized as spam !?! I just renamed the subject ! Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.) Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
