默认的设置是ISOLATION LEVEL 3
语法 SET TRANSACTION ISOLATION LEVEL 3;
最笨.官网的图不错看图吧
session 1 | session 2 |
|---|---|
;autocommit off AUTOCOMMIT IS OFF SET TRANSACTION ISOLATION LEVEL 3; Isolation level set to: REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES. | ;autocommit off AUTOCOMMIT IS OFF SET TRANSACTION ISOLATION LEVEL 3; Isolation level set to: REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES. |
--creating a table CREATE TABLE isol3_tbl(host_year integer, nation_code char(3)); CREATE UNIQUE INDEX on isol3_tbl(nation_code, host_year); INSERT INTO isol3_tbl VALUES (2008, 'AUS'); COMMIT; | |
--selecting records from the table SELECT * FROM isol3_tbl; host_year nation_code =================================== 2008 'AUS' | |
INSERT INTO isol3_tbl VALUES (2004, 'AUS'); INSERT INTO isol3_tbl VALUES (2000, 'NED'); /* able to insert new rows even if tran 2 uncommitted */ | |
SELECT * FROM isol3_tbl; host_year nation_code =================================== 2008 'AUS' 2004 'AUS' 2000 'NED' /* dirty read may occur so that tran_2 can select new rows uncommitted by tran_1 */ | |
ROLLBACK; | |
SELECT * FROM isol3_tbl; host_year nation_code =================================== 2008 'AUS' /* unrepeatable read may occur so that selected results are different */ | |
INSERT INTO isol3_tbl VALUES (1994, 'FRA'); DELETE FROM isol3_tbl WHERE nation_code = 'AUS' and host_year=2008; /* able to delete rows even if tran 2 uncommitted */ | |
SELECT * FROM isol3_tbl; host_year nation_code =================================== 1994 'FRA' | |
ALTER TABLE isol3_tbl ADD COLUMN gold INT; /* unable to alter the table schema until tran 2 committed */ | |
/* repeatable read is ensured while tran_1 is altering table schema */ SELECT * FROM isol3_tbl; host_year nation_code =================================== 1994 'FRA' | |
COMMIT; | |
SELECT * FROM isol3_tbl; | |
COMMIT; | host_year nation_code gold =================================== 1994 'FRA' NULL |