What is non-serializable schedule? in transaction database

Question

Can anyone explain me what is non-serializable in transaction DB. please give me an example. r1(x) r2(x)w1(y) c2 c1 is this non-serializable?

Answer 1

Imagine this table (in Oracle):

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 we start two READ COMMITTED transactions in two sessions:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

and issue the following queries:

-- session 1
UPDATE  t_series
SET     value = 1
WHERE   value = 2
/
COMMIT
/

and, then:

-- session 2
UPDATE  t_series
SET     value = 2
WHERE   value = 1
/
COMMIT
/

The outcome will be this:

id   value
1    2
2    2

, i. e. both records will have value = 2

The first query made both records to have value = 1, the second query saw these changes and made both records to have value = 2.

If we did the same with SERIALIZABLE level, the outcome would be this:

id   value
1    2
2    1

, i. e. the queries will just swap the value's

A serializable transaction sees the database in exactly same state it was when the transaction begun, except for the changes made with the transaction itself.