Ryan Stille wrote:
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.
See MySQL manual section 16.11.3:
"Consistent Non-Locking Read
A consistent read means that InnoDB uses its multi-versioning to
present to a query a snapshot of the database at a point in time.
The query will see the changes made by exactly those transactions
that committed before that point of time, and no changes made by
later or uncommitted transactions. The exception to this rule
is that the query will see the changes made by the transaction
itself that issues the query."
A simple way to demonstrate this is to make two connections to the same
database (that has InnoDB running and transactions enabled, default
settings are fine). Then ...
con1> create table t (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.04 sec)
con1> insert into t values (1);
Query OK, 1 row affected (0.02 sec)
con1> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
con2> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
### both connections see same data
### now start transaction on 2
con2> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)
### then insert on 1
con1> insert into t values (2);
Query OK, 1 row affected (0.02 sec)
con1> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
### but look at 2...
con2> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
### the modified data does not show up on 2 until
### the transaction is ended
con2> commit;
Query OK, 0 rows affected (0.00 sec)
con2> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)
This is not the same as locking the tables, since other connections are
free to continue to read and write to the same table(s) that are being
dumped. In my experience, there is a very minor performance hit
proportional to the difference between the "snapshot" and the "current"
data, but only in extreme cases is this even noticable. (Within my
current company, I regularly dump 1G+ of data inside a single
transaction w/o noticing any significant loss in server performance.)
Another point, if you lock the tables (ie, when dumping MyISAM tables),
simultaneous writes of any kind will not return immediately, but will
wait for the lock to be released. Obviously, this could cause serious
problems to your application if it relies on making any writes to the
database while you are dumping it. Thus, I would suggest using InnoDB as
much as possible.
-Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
