Transaction

What Is a Transaction

• Transaction 是一种将许多个SQL statements整合到一起执行的技术，当序列中某个statement出错时，会复原之前statement所做的更改。

• example (pseudocode)

  START TRANSACTION
  UPDATE account SET avail_balance = avail_balance - 500
  WHERE account_id = 9988
    AND avail_balance > 500
  IF <excatly one row was updated by the previous statement> THEN
    UPDATE account SET avail_balance = avail_balance + 500
      WHERE account_id = 9989;
  
      IF <excatly one row was updated by the previous statement> THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
  ELSE
    ROLLBACK;
  END IF;
  

  • 只有前面的query执行完毕，后续的query才能执行
  • 遇到任何的query出现bug时，则会使用rollback撤回之前的操作。
  • 当遇到commit和rollback执行前，服务器突然宕机，则会在重新登录时，执行rollback操作，撤回之前的操作
  • 当完成transaction发出commit的过程中，服务器突然宕机，则会在重启时，重新执行一次commit操作。

Starting a Transaction

• two ways of Transaction

  1. Transaction 和 database session 相联系，不需要明确声明Transaction开始，当最近的Transaction 结束，服务器会自动开始新的Transaction.
  2. 除非明确声明Transaction开始，否则，单独的SQL statement会被独立执行。为了声明Transaction开始，必须在开头加一行statement.

• 第一种方式的优势在于：当某些执行存在一定问题时，可以撤销之前的执行操作；MySQL采用第二种方式，因此，一旦单独的statement被执行，就无法通过Transection撤回。

• START TRANSACTION or BEGIN TRANSACTION
• 关闭auto-commit mode: SET AUTOCOMMIT = 0;

Ending a Transaction

• commit save change permanent
• rollback undo

• 其他导致Transaction停止的因素

  1. 服务器宕机，会在重启后自动roll back
  2. 采用SQL schema statement， 如ALERT TABLE 会导致之前的操作直接commited. 因此，SQL schema statement 必须在Transaction之外
  3. 新启一个start transaction，会导致之前的transaction直接commit
  4. 探测到 dead-lock 导致服务器停止对Transaction的运行，并执行rollback操作，并返回错误信息。
     • deadlock 当两个不同的transaction都在等待对方的lock资源时，例如：A Transaction刚写完account table，打算接下来得到transaction table的write lock，B Transaction 刚写完transaction，希望得到account table 的 write lock， 这样会导致两个transaction一直等待对方释放write lock，陷入死循环。当服务器检测到这个情况是，会掐断一个Transaction的进程，使其会退到执行之前的情况，让另一个Transaction先执行。
     • 降低deadlock出现频率的方法：尽量让所有的操作以同样的顺序进行。

Transaction Savepoints

MySQL 8.0+ Engines

• types of engines
  • MyISAM
    • nontransactional, table locking
  • MEMORY
    • nontransactional, in-memory tables
  • CSV
    • transactional, store data in comma-separed files
  • InnoDB
    • transactional, row-level locking
  • Merge
    • used to make a multiple identical MyISAM tables apear as a single table
  • Archive
    • used to store large amounts of unindexed data, mainly for archival purposes.

• Setting Engines

  • 查看table engine: SHOW TABLE STATUS LIKE '$tbl_name';
  • 设置table engine:
    • 新建: CREATE TABLE $tbl_name ($fld_setting) ENGINE = $engine_type;
    • 修改: ALERT TABLE $tbl_name ENGINE = $engine_type;

Savepoints

• create savepoint: SAVEPOINT $sp_name;
• roll back to a particular savepoint: ROLLBACK TO SAVEPOINT $sp_name;

• Example

  START TRANSACTION;
  
  UPDATE product
  SET date_retired = CURRENT_TIMESTAMP()
  WHERE product_cd = 'XYZ';
  
  SAVEPOINT before_close_accounts;
  
  UPDATE account
  SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),
    last_activity_date = CURRENT_TIMESTAMP()
  WHERE product_cd = 'XYZ';
  
  ROLLBACK TO SAVEPOINT before_close_accounts;
  COMMIT;