-- -------------------------------- The script used when storeMode is 'db' --------------------------------
-- the table to store GlobalSession data
CREATE TABLE IF NOT EXISTS public.global_table
(
xid VARCHAR(128) NOT NULL,
transaction_id BIGINT,
status SMALLINT NOT NULL,
application_id VARCHAR(32),
transaction_service_group VARCHAR(32),
transaction_name VARCHAR(128),
timeout INT,
begin_time BIGINT,
application_data VARCHAR(2000),
gmt_create TIMESTAMP(0),
gmt_modified TIMESTAMP(0),
CONSTRAINT pk_global_table PRIMARY KEY (xid)
);
CREATE INDEX idx_status_gmt_modified ON public.global_table (status, gmt_modified);
CREATE INDEX idx_transaction_id ON public.global_table (transaction_id);
-- the table to store BranchSession data
CREATE TABLE IF NOT EXISTS public.branch_table
(
branch_id BIGINT NOT NULL,
xid VARCHAR(128) NOT NULL,
transaction_id BIGINT,
resource_group_id VARCHAR(32),
resource_id VARCHAR(256),
branch_type VARCHAR(8),
status SMALLINT,
client_id VARCHAR(64),
application_data VARCHAR(2000),
gmt_create TIMESTAMP(6),
gmt_modified TIMESTAMP(6),
CONSTRAINT pk_branch_table PRIMARY KEY (branch_id)
);
CREATE INDEX idx_xid ON public.branch_table (xid);
-- the table to store lock data
CREATE TABLE IF NOT EXISTS public.lock_table
(
row_key VARCHAR(128) NOT NULL,
xid VARCHAR(128),
transaction_id BIGINT,
branch_id BIGINT NOT NULL,
resource_id VARCHAR(256),
table_name VARCHAR(32),
pk VARCHAR(36),
status SMALLINT NOT NULL DEFAULT 0,
gmt_create TIMESTAMP(0),
gmt_modified TIMESTAMP(0),
CONSTRAINT pk_lock_table PRIMARY KEY (row_key)
);
comment on column public.lock_table.status is '0:locked ,1:rollbacking';
CREATE INDEX idx_branch_id ON public.lock_table (branch_id);
CREATE INDEX idx_xid ON public.lock_table (xid);
CREATE INDEX idx_status ON public.lock_table (status);
CREATE TABLE distributed_lock (
lock_key VARCHAR(20) NOT NULL,
lock_value VARCHAR(20) NOT NULL,
expire BIGINT NOT NULL,
CONSTRAINT pk_distributed_lock_table PRIMARY KEY (lock_key)
);
INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('AsyncCommitting', ' ', 0);
INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('RetryCommitting', ' ', 0);
INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('RetryRollbacking', ' ', 0);
INSERT INTO distributed_lock (lock_key, lock_value, expire) VALUES ('TxTimeoutCheck', ' ', 0);
--该sql需要在所有客户端执行
-- for AT mode you must to init this sql for you business database. the seata server not need it.
CREATE TABLE IF NOT EXISTS public.undo_log
(
id SERIAL NOT NULL,
branch_id BIGINT NOT NULL,
xid VARCHAR(128) NOT NULL,
context VARCHAR(128) NOT NULL,
rollback_info BYTEA NOT NULL,
log_status INT NOT NULL,
log_created TIMESTAMP(0) NOT NULL,
log_modified TIMESTAMP(0) NOT NULL,
CONSTRAINT pk_undo_log PRIMARY KEY (id),
CONSTRAINT ux_undo_log UNIQUE (xid, branch_id)
);
CREATE SEQUENCE IF NOT EXISTS undo_log_id_seq INCREMENT BY 1 MINVALUE 1 ;
pgsql集成seata AT模式 初始化脚本
于 2023-04-20 15:31:16 首次发布