"Ryan Stille" <[EMAIL PROTECTED]> wrote on 07/21/2005 05:47:28 PM:
> I'm trying to get a handle on MySQL backups and hot backups using MyISAM > and InnoDB tables together. We plan to switch from SQL Server to MySQL > soon. > > How are you guys handling full-backups of databases with mixed MyISAM > and InnoDB tables? From what I've read (and I've been reading a lot), > if we are using only one or the other then it is a pretty simple matter > to get a clean backup. Use --lock-tables for MyISAM, or > --single-transaction if we using only InnoDB tables. > > I've been doing some testing and came across something I don't > understand. I filled my test InnoDB formatted table with a lot of data > so mysqldump will take a while to complete. Then I start mysqldump on > this database with the --single-transaction option. While that is > running, I insert a record into the table. It completes sucessfully. I > then run a query and am able to see that record in the database. The > mysqldump is still running. How is this record getting inserted into > the database? I thought it was locked while the dump was happening? I > thought it would get queued up and inserted when the mysqldump is > finished. The record was NOT in the dump, this part made sense. > > Thanks for any help. > -Ryan > > While mysqldump is doing its thing, it has a transaction open. When you performed your update, you started a second transaction. The first transaction is isolated from the changes that appear in the second just as the second can't see any changes that happen in the first. Transacted changes will only be written into the database when the transaction commits. Let me walk you through what I think was going on: T1: mysqldump gets a read lock on every table in the database T2: You insert a record. T3: You looked for the record you just inserted and found it. T1: mysqldump finishes and commits the transaction Did that make sense? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
