方法

pandasでのトランザクションは、SQLAlchemyのengine.begin()を使って、以下のように実装できます。

こうすることで、各テーブルの更新に時間が掛かってしまうような場合でも時間差なく同時に更新を反映できますし、失敗時にはロールバックもできます。
withを使うことで、自動的にコミットの処理が呼び出されます。(失敗時はロールバック)

from sqlalchemy import create_engine
engine = create_engine(url)

with engine.begin() as conn:
    df1.to_sql('table1', conn, if_exists='append')
    df2.to_sql('table2', conn, if_exists='append')

注意点

withブロックの中でコミットが走ってしまうようなクエリを流してしまうと、そこでコミットされてしまいます。
参考: Statements That Cause an Implicit Commit

例えば、to_sql のオプションif_exists を'replace'にした場合は、DROP TABLEが走るので、テーブルごとに更新が発生してしまいます。

with engine.begin() as conn:
    df1.to_sql('table1', conn, if_exists='replace')
    df2.to_sql('table2', conn, if_exists='replace')  # DROP TABLEが呼び出されて、table1の更新がコミットされてしまう。

テーブルの中身を全部更新する場合は、一度別名でテーブルを作ってRENAME TABLEで入れ替えるのが良いようです。

from sqlalchemy import create_engine
engine = create_engine(url)

df1.to_sql('table1_new', engine, if_exists='replace')
df2.to_sql('table2_new', engine, if_exists='replace') 

engine.execute('''
RENAME TABLE table1 to table1_old, 
             table1_new to table1
             table2 to table2_old,
             table2_tmp to table2;
'''
)