The highest isolation level (6). Problems concerning concurrency (e.g. dirty read, non-repeatable read, phantom read, etc.) do not occur.
The following are the rules of this isolation level:
This isolation level uses a two-phase locking protocol for shared and exclusive lock: the lock is maintained until the transaction ends even after the operation has been executed.
The following example shows that another transaction cannot access the table or record while one transaction is reading or updating the object when the transaction level of the concurrent transactions is SERIALIZABLE.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 6;
Isolation level set to: SERIALIZABLE |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 6;
Isolation level set to: SERIALIZABLE |
--creating a table
CREATE TABLE isol6_tbl(host_year integer, nation_code char(3));
INSERT INTO isol6_tbl VALUES (2008, 'AUS');
COMMIT; |
|
|
--selecting records from the table SELECT * FROM isol6_tbl WHERE nation_code = 'AUS'; host_year nation_code =================================== 2008 'AUS' |
INSERT INTO isol6_tbl VALUES (2004, 'AUS'); /* unable to insert a row until the tran 2 committed */ |
|
|
COMMIT; |
|
SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';
/* unable to select rows until tran 1 committed */ |
COMMIT; |
host_year nation_code =================================== 2008 'AUS' 2004 'AUS' |
DELETE FROM isol6_tbl WHERE nation_code = 'AUS' and host_year=2008; /* unable to delete rows until tran 2 committed */ |
|
|
COMMIT; |
|
SELECT * FROM isol6_tbl WHERE nation_code = 'AUS'; /* unable to select rows until tran 1 committed */ |
COMMIT; |
host_year nation_code =================================== 2004 'AUS' |
ALTER TABLE isol6_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 isol6_tbl WHERE nation_code = 'AUS'; host_year nation_code =================================== 2004 'AUS' |
|
COMMIT; |
|
SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';
/* unable to access the table until tran_1 committed */ |
COMMIT; |
host_year nation_code gold =================================== 2004 'AUS' NULL |