How to make transactions atomic in Mysql 5.6

Question

How do we make the following transactions atomic?

conn - is a connection to the old primary, which is being reset as replica now.

Python code

conn.execute('unlock tables')
conn.cursor().execute('stop slave')
conn.cursor().execute('reset master')

conn.autocommit = True is set during conn declaration

While running these commands. As soon as I run unlock tables on old primary, due to stale connections there could be some transactions written to old primary database. Which are being purged when we run reset master. These are audit transactions and not of much significance. However, before demoting primary to slave we need to make sure absolutely no transactions are written even if there are stale connections or DNS cache resolves to old primary.

Is there any way to run these commands atomic ? for example

conn.cursor().execute("unlock tables; stop slave; reset master", multi=True)

Would this ensure no transactions are written while we open up database to run command to stop slave and reset master ?

To lock database completely we need to run "Flush tables with read lock" (FTWRL). However, if we have FTWRL, we can't run write commands like stop slave.

Answer 1

By inspecting the bin logs and transactions I came to know that multi=True would execute transactions under different GTID's.

To execute in a single transaction we need to use begin_transaction

example

conn.start_transaction()
sql1 = "update ***"
sql2 = "insert ***"
cursor.execute(sql1)
cursor.execute(sql2)
db.commit()

Answer 2

DDLs cannot be in transactions; they effectively terminate any open transactions. 8.0 fixes some of that.

I have never found a need for RESET SLAVE; what is your purpose in doing it?

When switching Primaries or doing certain other things, consider setting readonly on the writable Primary.