Can anybody explain me what's non-serializable in transaction DB. please produce a good example. r1(x) r2(x)w1(y) c2 c1 is non-serializable?
Picture this table (in
CREATE TABLE t_series (id INT NOT NULL PRIMARY KEY, value INT NOT NULL) INSERT INTO t_series VALUES (1, 1) INSERT INTO t_series VALUES (2, 2)
Now starting two
READ COMMITTED transactions in 2 periods:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
and problem the next queries:
-- session 1 UPDATE t_series SET value = 1 WHERE value = 2 / COMMIT /
-- session 2 UPDATE t_series SET value = 2 WHERE value = 1 / COMMIT /
The end result is going to be this:
id value 1 2 2 2
, i. e. both records may have
value = 2
The very first query made both records to possess
value = 1, the 2nd query saw these changes making both records to possess
value = 2.
When we did exactly the same with
SERIALIZABLE level, the end result could be this:
id value 1 2 2 1
, i. e. the queries will just swap the
A serializable transaction sees the database in exactly same condition it had been once the transaction had begun, aside from the alterations produced by the transaction itself.