Transaction

用於確保資料的完整性、一致性，同筆資料可能涉及多張table，若其中一次儲存資訊失敗，則可能導致資料不完整，因此通常會使用batch式儲存的 Transaction 方式，每一次的儲存可被視為一筆交易，若是交易失敗則通通恢復原狀，也不需要存入資料庫。

使用方式

1. key word: BEGIN, ROLLBACK, COMMIT
   -> BEGIN 或 START TRANSACTION：用於開始一個交易的起點
   -> ROLLBACK：取消先前的變更
   -> COMMIT：確認提交交易，使變更生效
2. 改變MySQL的自動提交設定:
   SET AUTOCOMMIT=1; 每次的指令自動確認，不需commit但無法rollback
   SET AUTOCOMMIT=0; 每次的指令不會自動確認，需commit但可rollback

範例(python+mysql)

datalists = [
    ['Amy', 'iotyk', 'kkk@gmail.com'], 
    ['Kamy', 'itggyk', 'ggk@gmail.com'], 
    ['Mummy', 'ittyyhk', 'jkkkk@gmail.com']
]

import pymysql

try:
    # Establishing the connection
    conn = pymysql.connect(
        host='localhost',
        database='hackathon',
        user='root',
        password='',
        charset='utf8'
    )
    conn.autocommit = False
    cursor = conn.cursor()
                                   
    # SQL command for insertion
    command = "INSERT INTO user(username, password, email) VALUES(%s, %s, %s)"
    
    # Assuming datalists is defined elsewhere in your code
    for data in datalists:
        cursor.execute(command, (data[0], data[1], data[2]))

    # Commit the transaction
    conn.commit()

except pymysql.MySQLError as error:
    print("Failed to update record to database, rolling back: {}".format(error))
    # Reverting changes because of exception
    conn.rollback()

finally:
    # Closing database connection and cursor
    if conn:
        cursor.close()
        conn.close()
        print("Connection is closed")

【輸出結果】

Reference

• 【MySQL】Transaction a.k.a 交易or事務
• Use Commit and Rollback to Manage MySQL Transactions in Python