When does MySQL server reply to client that a SQL statement has completed or a transaction has been committed?
Before it writes to binary log or after?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
In general, Mysql writes to the binary log at the end of the transaction. So if a client opens an explicit transaction the statements will be written into the binary log only after a
commit. But if the statements involve nontransactional tables (MyIsam) it is possible that they are written to the binary log before the end of the transaction. If you have autocommit enabled, Mysql replies to client after a statement is written to the binary log, because every statement is a transaction.
Using mysqlbinlog command utility you can do the following experiment. Open a shell with a command like this:
mysqlbinlog --read-from-remote-server --stop-never -h<host> -P<port> -u<user> -p<passwordd> binary-log.000003
Into another shell you can launch mysql client.
mysql> create table tr (i int) engine=innodb; mysql> insert into tr values(1),(2),(3);
You could see the output of mysqlbinlog scrolling immediately with a text like this:
# at 1016 [..] SET TIMESTAMP=1458127130/*!*/; create table tr (i int) engine=innodb [...] # at 1281 [..] SET TIMESTAMP=1458127136/*!*/; BEGIN /*!*/; # at 1384 [..] SET TIMESTAMP=1458127136/*!*/; insert into tr values(1),(2),(3) /*!*/; # at 1514 [...] COMMIT/*!*/;
If you try:
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tr values(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.01 sec)
the output of mysqlbinlog scrolls down only after the
If you try
create table notr (i int) engine=myisam; and try to insert into notr , mysql writes immediately to binlog independently if the insert is inner a transaction. And the behavior can be different if you mix notransaction and transactional tables.
Hope this help.