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?

1 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/*!*/;
# at 1384
SET TIMESTAMP=1458127136/*!*/;
insert into tr values(1),(2),(3)
# at 1514

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.