CREATE TABLE "public"."users" (
  "user_account" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "username" varchar(32) COLLATE "pg_catalog"."default",
  "user_avatar" varchar(64) COLLATE "pg_catalog"."default",
  "user_profile" varchar(512) COLLATE "pg_catalog"."default",
  "hashed_password" varchar COLLATE "pg_catalog"."default" NOT NULL
);

BEGIN; -- 開啟事務(wù)

-- SQL 語句
INSERT INTO users (user_account, hashed_password) VALUES ('Alice', 'xxxx');

COMMIT; -- 提交事務(wù)

BEGIN

DELETE FROM users;

ROLLBACK;

BEGIN;

INSERT INTO users (user_account, hashed_password) VALUES ('Alice', 'xxxx');
SAVEPOINT sp1; -- 創(chuàng)建回滾點(diǎn)

INSERT INTO users (user_account, hashed_password) VALUES ('Alice2', 'xxxx');
SAVEPOINT sp2;

INSERT INTO users (user_account, hashed_password) VALUES ('Alice3', 'xxxx');
ROLLBACK TO sp2;

COMMIT; -- 提交事務(wù)

BEGIN; -- 事務(wù)1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE users SET hashed_password = 'new_hash' WHERE user_account = 'Alice';
SELECT txid_current(); -- 查看當(dāng)前事務(wù)id
-- 保持事務(wù)未提交

BEGIN; -- 事務(wù)2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM users WHERE user_account = 'Alice';
SELECT txid_current();


ROLLBACK; -- 釋放鎖，事務(wù) 2 的查詢繼續(xù)執(zhí)行

BEGIN;
SELECT hashed_password FROM users;

BEGIN;
UPDATE users SET hashed_password = 'xxxx';
COMMIT;

SELECT hashed_password FROM users;

BEGIN;
SELECT COUNT(*) FROM users; -- 假設(shè)是3

BEGIN;
INSERT INTO users (user_account, hashed_password) VALUES ('Alice4', 'xxxx');
COMMIT; -- 增加到4

SELECT COUNT(*) FROM users; -- 增加到4

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SQL 語句
COMMIT;

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

default_transaction_isolation = 'read committed'

SET AUTOCOMMIT TO OFF;