Sql Isolation and Migration

sql Isolation and Migration info


isolation for database

if a weak isolation

Read phenomena(現象)

  1. Dirty Read A transaction reads data written by other concurrent(併發,同時)uncommitt transaction
  2. NoN-Repeateable Read A transaction reads the same row twice and sees different value because it has been modified by other commit transaction
  3. Phantom(幻影) Read A transaction re-executes a query to find rows that satisfy a condition(狀況) and sees a different set of rows, due to changes by other committed transaction
  4. Serialization(序列化) Anomaly The result of a group of concurrent committed transactions is impossible to achieve if we try to run them sequentially(依次) in any order without overlapping(重疊)

four standard isolation levels

American National Standards institute - ANSL
 
Low ———>High
  1. Read uncommitted can see data written by uncommitted transaction ⇒ possible have dirty read
  2. Read committed Only see data written by committed transaction ⇒impossible have dirty read
  3. Repeatable read Same read query always returns same result
  4. Serializable Can achieve same result if execute transaction serially in some order instead of concurrent

SQL 操作isoaltion

select @@transaction_isolation =>查看特定隔離
 
select @@global.transaction_isolation =>查看全局隔離
 
SET session transaction isolation level read [isolation_name] =>設定隔離級別
-- 此更改只會對當前會話的所以未來交易產生影響,不適用在另一個mysql控制台會話上運行的事物

Read uncommitted級別

set session transaction isolation level read uncommitted
 
begin --開始交易,也可以使用start transaction
 
//預設有資料表account中的一個帳戶具備的balance有100元
update account set balance = balance - 10 where id = 1
 
//此時開啟兩個mysql-controller-window
//查詢同比資料
select * from account
 
發現查詢到的兩筆數據都是相同的,在修改的窗口並未committ前
=>因此這是一個髒讀,因為使用了read-uncommitted的隔離級別
 

Read committed級別

set session transaction level read committed
 
begin
 
update account set balance = balance - 10 where id = 1
 
//另一個窗口查詢
select * from account where id =1
 
會發現balance並沒有改變
=>這是因為隔離級在read committed,除非commit否則不影響其他窗口(防止髒讀)

Repeatable read 級別(確保數據一致性

⇒當其中一個窗口進行資料改變,會發現另一個窗口的資料不變
 
   防止幻讀,因此查詢相同東西,結果不變
 
Ex:
 
窗口1已經將balance從80改為70並且commit
 
窗口2查詢balance ≥ 80 的,會返回窗口1修改的balance但值還是80
 
此時更新窗口2的balance為 (balance-10) 並查詢會發現balance為60
 
⇒這是因為窗口1已經更改且commit了

Serializable級別

EX:
 
窗口2進行查詢並且指定id=1的row
 
窗口1進行更新的動作⇒此時block,窗口2的選擇查詢阻塞了窗口1的更新
 
⇒因為在serializable級別中的select都將轉換成select for share
 
   只要持有select for share就只允許其他事物的讀取,不允許更新或刪除
 
   不可能再現數據不一治的情況
 
(鎖具有時間限制,在時間限制內沒有rollback或commit釋放鎖會引發timeout報錯
 
在窗口2未提交的情況下(此時還具備select for share的鎖
 
此時窗口2進行和窗口1所指定的相同對象進行更新⇒因發死鎖

DB MIGRATION (DB模式遷移)

brew install golang-migrate
 
常用指令
Commands:
  create [-ext E] [-dir D] [-seq] [-digits N] [-format] NAME
               Create a set of timestamped up/down migrations titled NAME, in directory D with extension E.
               Use -seq option to generate sequential up/down migrations with N digits.
               Use -format option to specify a Go time format string.
  goto V       Migrate to version V
  up [N]       Apply all or N up migrations
 
migrate -version 查看版本
 
migrate create -ext sql -dir db/migration -seq init_schema創立遷移,使用seq設定遷移連續版本號
=>會發現一個開啟,一個關閉

遷移前先檢查postgres的container是否運行

docker ps
docker stop <container_name_or_id> 關閉container
docker ps -a 列出所有容器,無論狀態
docker start <container_name_or_id>運行container
 
docker exec -it postgres12 /bin/sh 訪問shell,可以執行linux命令

在shell中創建資料庫

createdb --username=root --owner=root simple_bank
 
--username 選項表示以什麼身份連接,這邊使用root
--owner    選項表示這資料庫屬於,這邊同樣屬於root
simplebank為資料庫名稱

pqsl命令訪問

psql simple_bank 訪問控制台
dropdb simple_bank 刪除simple_bank這個資料庫
\q退出psql
exit 退出shell
 
直接在docker創建資料庫
 
docker exec -it postgres12 createdb --username=root --ownew=root simple_bank
 
訪問數據庫
 
docker exec -it postgres12 psql -U root simple_bank
 
刪除
docker rm <container_name_or_id>
 
啟動新postgres容器
make postgres

上遷移以及下遷移

up-script and down-script
 
        migrate up→x.up.sql→
 
Old DB schema ——> Migration version x ——>New DB schema
 
Old DB schema <——Migration version x <——New DB schema
 
                                                     <-x.down.sql<-migrate down
 
Migrate up→ db/migration 中的 up-script將按其前綴版本的順序依次運行
 
Migrate down → db/migrate 中的 down-script 將按其版本相反順序運行

migrate up 表

CREATE TABLE "account" (
                           "id" bigserial PRIMARY KEY,
                           "owner" varchar NOT NULL,
                           "balance" bigint NOT NULL,
                           "currency" varchar NOT NULL,
                           "create_at" timestamptz DEFAULT (now())
);
 
CREATE TABLE "entries" (
                           "id" bigserial PRIMARY KEY,
                           "account_id" bigint NOT NULL,
                           "account" bigint NOT NULL,
                           "create_at" timestamptz NOT NULL DEFAULT (now())
);
 
CREATE TABLE "transfers" (
                             "id" bigserial PRIMARY KEY,
                             "form_account_id" bigint NOT NULL,
                             "to_account_id" bigint NOT NULL,
                             "amount" bigint NOT NULL,
                             "create_at" timestamptz NOT NULL DEFAULT (now())
);
 
CREATE INDEX ON "account" ("owner");
 
CREATE INDEX ON "entries" ("account_id");
 
CREATE INDEX ON "transfers" ("form_account_id");
 
CREATE INDEX ON "transfers" ("to_account_id");
 
CREATE INDEX ON "transfers" ("form_account_id", "to_account_id");
 
COMMENT ON COLUMN "entries"."account" IS 'can be postive or negative';
 
COMMENT ON COLUMN "transfers"."amount" IS 'it must be postive';
 
ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "account" ("id");
 
ALTER TABLE "transfers" ADD FOREIGN KEY ("form_account_id") REFERENCES "account" ("id");
 
ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "account" ("id");

migrate down 表

DROP TABLE IF EXISTS enteries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;
/*
    在本例中,up腳本創建了三個表
    因此down必須刪除這三個表,因此使用DROP
    我們在刪除帳戶表前先刪除條目和轉移表,因為條目和轉移表間存在外鍵約束引用帳戶表
*/
 

操作makefile

postgres:
 docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=password -d postgres:12-alpine
createdb:
 docker exec -it postgres12 createdb --username=root --owner=root simple_bank
dropdb:
 docker exec -it postgres12 dropdb simple_bank
 
.PHONY: postgres createdb dropdb
  1. docker rm <container_name_or_id>
  2. check container docker ps -a
  3. make postgres
  4. make createdb
  5. use table plus connect the database
  6. click sql icon to open the new simple_bank base
  7. back to terminal to do once migrate(遷移)
  8. use instruct to do up-script of migrate 9.dont forget to set sslmode=disable because postgres is not default setting
migrate -path db/migration -database "postgresql://root:password@localhost:5432/simple_bank?sslmode=disable" -verbose up
  • schema_migrations 資料表儲存最新應用的遷移版本

makefile總覽

postgres:
 docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=password -d postgres:12-alpine
createdb:
 docker exec -it postgres12 createdb --username=root --owner=root simple_bank
dropdb:
 docker exec -it postgres12 dropdb simple_bank
 
migrateup:
 migrate -path db/migration -database "postgresql://root:password@localhost:5432/simple_bank?sslmode=disable" -verbose up
 
migratedown:
 migrate -path db/migration -database "postgresql://root:password@localhost:5432/simple_bank?sslmode=disable" -verbose down
 
.PHONY: postgres createdb dropdb migrateup migratedown