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.

  • Why are you using auto commit if you want several statements to be atomic? I dont know if the statements you are mentioning respect transactions, but auto commit seems like a bad start May 17, 2021 at 22:16
  • yeah irrespective of autocommit flag for database, seems like start_transaction() is the right thing to use
    – RobinHood
    May 17, 2021 at 22:37
  • You've probably come across this aready that lock tables, stop slave, reset master aren't transaction capable. Only insert/delete/update/replace/select and the like are.
    – danblack
    May 17, 2021 at 23:35
  • That's correct. Concern here is as soon as we run unlock tables, there could be a new transaction inserted before i run reset master. Which is why I wanted to make them atomic. But as you suggested I think having autocommit = True is a blocker
    – RobinHood
    May 19, 2021 at 22:11
  • But why did you lock the tables in the first place?
    – Rick James
    Jun 9, 2021 at 21:47

2 Answers 2


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


sql1 = "update ***"
sql2 = "insert ***"

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.

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.