mysql study

ALTER table 是鎖住整個 table

# 1. 檢查支票帳戶餘額是否大於等於200
# 2. 從支票帳戶餘額扣200轉出
# 3. 從存款帳戶餘額加200轉入

簡單的transaction
START TRANSACTION;
select balance from checking where customer_id = 1 and balace >= 200;
update checking set balance  = balance - 200 where where customer_id = 1;
update savings set balance = balance + 200 where customer_id = 1;
COMMIT;

隔離級別

1. READ_UNCOMMITTED (未提交讀)

• 一個transaction中修改的東西, 在其他 transaction中是可見的, 也就其他事務可以讀取另一個事務尚未提交 (commit) 的東西, 也被稱作 dirty read, 容易造成很多問題, 效能也不一定會好

1. READ_COMMITTED
• 一個transaction在committed之前所做的任何改變都不會被其他transaction看到
2. REPEATABLE READ
• 保證了一個transaction讀取同樣的record會是一樣的結果, 理論上無法解決Phantom Read (幻讀)的問題, 幻讀是某個transaction A在讀取某個範圍的資料時, 別的transaction剛好在這個範圍內新增一筆資料, 造成這個transaction A會產生Phantom Row, InnoDB透過多版本併發控制 (Multi Version Concurrency Control) 解決幻讀的問題
3. SERIALIZABLE
• 最高的隔離級別, 強迫所有transaction一個接一個執行, 就不會有Phantom Read的問題, 也就是在讀取的每一行都加上read lock(讀鎖), 會導致大量超時與搶鎖的問題,只有在非常強調資料一致性與可以接受非併發環境才可以考慮使用

MySQL預設開啟 autocommit

show variables like 'AUTOCOMMIT';

MySQL 可以使用 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;
改變隔離級別

********
建議  事務中盡量不要用 autocommit,
只可以使用 lock tables, 其他地方就不要輕易使用 lock tables
********

MVCC (MultiVersion Concurrecy Control) 的實現透過保存資料在某個時間點的快照,
也就是不管執行多長時間, 某個transaction內的看到的數據都是一樣的!
在不同的時間點, transactio 內看到的資料都不同

InnoDB 實作步驟原文如下: Ref

# InnoDB implements MVCC by storing with each row two additional,
# hidden values that record when the row was created and when it was expired (or deleted). 
# Rather than storing the actual times at which these events occurred,
# the row stores the system version number at the time each event occurred.
# This is a number that increments each time a transaction begins.
# Each transaction keeps its own record of the current system version,
# as of the time it began. Each query has to check each row’s version numbers against the transaction’s version.

# SELECT
#   - InnoDB must examine each row to ensure that it meets two criteria:
#     InnoDB must find a version of the row that is at least as old as the transaction 
#     (i.e., its version must be less than or equal to the transaction’s version). 
#     This ensures that either the row existed before the transaction began, 
#     or the transaction created or altered the row.

#   - The row’s deletion version must be undefined or greater than the transaction’s version. 
#     This ensures that the row wasn’t deleted before the transaction began.

#   - Rows that pass both tests may be returned as the query’s result.

# INSERT
#   - InnoDB records the current system version number with the new row.

# DELETE
#   - InnoDB records the current system version number as the row’s deletion ID.

# UPDATE
#   - InnoDB writes a new copy of the row, using the system version number for the new row’s version. 
#     It also writes the system version number as the old row’s deletion version.

InnoDB MVCC大概實作流程

1. 在資料新增時, 會在每個record後面加兩個隱藏的值
• 這筆資料被建立的時間
• 這筆資料過期(或刪除)的時間
2. 實際看一下在 REPEATABLE READ 的隔離級別下, MVCC具體是如何運作的

• Select
• InnoDB只找 <= 目前transaction版本的資料, 這樣就可以確保資料是在transaction前就存在, 或是由目前的transaction所插入的
• 資料的刪除版本要 >= 目前transaction版本, 也就是要嗎是未定義的, 不然就是剛好等於目前transaction版本, 這樣可以確保transaction讀取到的資料是在transaction開始前還未被刪除
• 符合以上兩點的資料才會被撈出來
• Insert
• InnoDB為每一筆新的資料保存目前的版本號
• Delete
• InnoDB為把每一筆被刪除的資料的刪除ID更新為目前版本號
• Update
• InnoDB會寫入一筆新的資料副本, 並將這邊新的資料的建立版本號, 刪除ID的版本號則是使用舊的
• 這兩個額外的欄位的儲存是不需要用到鎖的, 好處是效能比較好, 也只會取到符合條件的資料, 缺點就是要多額外的空間去儲存與維護資料

InnoDB 是基於clustered index所建立的, clustered index對於primary key的查詢速度非常的快, 但 secondary index會包含primary key, 所以如果primary key很肥的話, secondary index會更肥, 所以說如果這張表需要建立很多索引時, primary key應該越小越好, InnoDB不會壓縮index的!

任何改變資料表結構的行為, 在 InnoDB 都會重新建立整張表, 連index一起重建

如果資料量不大的話, 想要改變engine或是alter table的話
可以使用

# 資料量小
CREATE TABLE innodb_tble like myisam_table;
ALTER TABLE innodb_tble ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;

# 資料量大的話要採取transaction + 分段
# 有必要的話, 須將原本的table上鎖來確保資料一致性
START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id between x an y;
COMMIT;