Is reply to client about transaction commit, before binary log or after?

Question

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?

Answer 1

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 commit.

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.