Sql Isolation and Migration
sql Isolation and Migration info
isolation for database
if a weak isolation
Read phenomena(現象)
- Dirty Read A transaction reads data written by other concurrent(併發,同時)uncommitt transaction
- NoN-Repeateable Read A transaction reads the same row twice and sees different value because it has been modified by other commit transaction
- 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
- 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
- Read uncommitted can see data written by uncommitted transaction ⇒ possible have dirty read
- Read committed Only see data written by committed transaction ⇒impossible have dirty read
- Repeatable read Same read query always returns same result
- 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
- docker rm
<container_name_or_id>
- check container
docker ps -a
- make postgres
- make createdb
- use table plus connect the database
- click sql icon to open the new simple_bank base
- back to terminal to do once migrate(遷移)
- 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