Transcript Set Transaction Statement

Transaction Support in SQL
Database Management Systems II,
Hyunja Lee
1
Lifetime of a Transaction in SQL

No explicit “begin” statement.
– a new transaction is implicitly began when the first SQL statement is
executed.

Explicit “end” statement
– Commit or Rollback ends the transaction.

Oracle 9i (using sqlplus)
SQLPLUS> commit;
SQLPLUS> select * from proj; // new trasaction is began
SQLPLUS> insert into proj values (1, ‘Proj_X’, 500, ‘John’);
SQLPLUS> commit; // the transaction is committed and ended.
Database Management Systems II,
Hyunja Lee
2
Set Transaction Statement

Characteristics of a transaction is set in SET
TRANSACTION statement
– Must appear as the first statement in a transaction.
– Can set up the following characteristics
 Access mode :
read only or read write
 Isolation
level :
serializable, repeatable read, read committed, or read uncommitted.
Database Management Systems II,
Hyunja Lee
3
Set Transaction Statement (Read
only/ Read Write)

Access mode : read only or read write
– Set transaction read only : set the transaction (that you now
begin) as a transaction that contains only read operations
(select statements).
 No write operation (insert, delete, update statements) is
allowed.
 See changes committed before this transaction began.
– Set transaction read write : set the transaction as a transaction
that contains read/write operations.
 default setting.
Database Management Systems II,
Hyunja Lee
4
Set Transaction Statement (Read
only/ Read Write) Cont’d

Wait a second….
– Why do we want to set up a transaction as read only ? What
about setting up a transaction as read-write and invoking
read operations only?
commit;
select projno from proj;
commit;
insert into proj (2,’Project_Y’,100, ‘Tom’);
commit;
select count(*) from proj;
commit;

Count(*) will give you one more number of which
tuples you have seen from the previous select
statements. Why? A new tuple is inserted !!!!!!
Database Management Systems II,
Hyunja Lee
5
Set Transaction Statement (Read
only/ Read Write) Cont’d

Wait another second….
– Didn’t we learn that transactions should always be
serialized ? In other words, isn’t is true that DBMS should
support a serializable schedule automatically?
T1
commit;
select projno from proj;
T2
select count(*) from proj;
commit;

commit;
insert into proj (2,’Project_Y’,100, ‘Tom’);
commit;
If DBMS supports the serializability, then the result
should be the same as either T1 and T2, or T2 and T1.
Database Management Systems II,
Hyunja Lee
6
Set Transaction Statement (Read
only/ Read Write) Cont’d

In reality, the answer is NOT!!!! Why not? Supporting
the serializability is expensive & sometimes not
desirable.

For example, Oracle DBMS does not have the
serializable schedule as the default schedule. It
employs read-committed isolation level as the default.
Database Management Systems II,
Hyunja Lee
7
Set Transaction Statement (Read
only/ Read Write) Cont’d

What if we set the transaction as read only?
commit;
set transaction read only;
select projno from proj;
select count(*) from proj;
commit;
commit;
insert into proj (2,’Project_Y’,100, ‘Tom’);
commit;

DBMS allows a read-only transaction to see the
changes committed before the transaction began.

Oracle calls this as transaction-level read consistency (versus
statement-level read consistency).
Database Management Systems II,
Hyunja Lee
8
Set Transaction Statement (Isolation
level)

Isolation level of a transaction is set in SET
TRANSACTION statement
– Serializable: default in SQL standard.
– Repeatable Read: prevents non-repeatable read.
– Read Committed: default in Oracle DBMS.
 See changes only committed by another transactions.
 Prevents dirty-read anomaly.
– Read Uncommitted:
 See changes incurred by any (including uncommitted)
transactions.
Database Management Systems II,
Hyunja Lee
9
Set Transaction Statement (Isolation
level) Cont’d

T1
Serializable
commit;
SET TRANSACTION
ISOLATION LEVEL
SERIALIZABLE;
select projno from proj;
T2
select count(*) from proj;
commit;


commit;
insert into proj (2,’Project_Y’,100, ‘Tom’);
commit;
If DBMS supports the serializability, then the result should be
the same as either T1 and T2, or T2 and T1.
Lock the proj table in S mode so that any write operation to the
proj table is not allowed. => prevent phantom.
Database Management Systems II,
Hyunja Lee
10
Set Transaction Statement (Isolation
level) Cont’d

T1
Repeatable Read
commit;
select budget from proj where
projno=2;
T2
select budget from proj where
projno=2;
commit;


commit;
update proj set budget = 200 where
projno=2;
commit;
Non repeatable read : the budget value of the first select is
different to the budget value of the second select.
If the isolation level is set to REPEATABLE READ, then two
budget values are the same. That’s why it is called repeatable
read.
Database Management Systems II,
Hyunja Lee
11
Set Transaction Statement (Isolation
level) Cont’d

T1
Read Committed
commit;
select budget from proj where
projno=2;
update proj set budget =
budget+100 where
projno=2;
commit;
T2
select budget from proj where projno=2;
update proj set budget = budget+100
where projno=2;
rollback;
Dirty read : T2 reads the budget written by T1 which has not
committed.
Database Management Systems II, Hyunja Lee

12
Set Transaction Statement (Isolation
level) Cont’d
Oracle9i supports
Type of anomaly
Isolation level Dirty Read
stricter
looser
Serializable
No
Nonrepeatable
read
No
Repeatable
Read
Read
Committed
No
No
Yes
No
Yes
Yes
Read
Yes
Uncommitted
Yes
Yes
Database Management Systems II,
Hyunja Lee
Phantom
No
13
A Sample SQL Transaction

Note: This example is based on the embedded SQL syntax.
This can not be run in Oracle sqlplus.
EXEC SQL WHENEVER SQLERROR GOTO UNDO;
EXEC SQL SET TRANSACTION
READ WRITE
ISOLATION LEVEL SERIALIZABLE;
EXEC SQL INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, DNO, SALARY)
VALUES (‘Robert’, ‘Smith’, ‘991001234’, 2, 35000);
EXEC SQL UPDATE EMPLOYEE SET SALARY = SALARY * 1.1 WHERE DNO
= 2;
EXEC SQL COMMIT;
GOTO THE_END;
UNDO: EXEC SQL ROOBACK;
THE_END:
Database Management Systems II,
Hyunja Lee
14
A Sample SQL Transaction Cont’d

Note: You may run a part of this following block in Oracle sqlplus.
But how about the control (in italic)?
EXEC SQL WHENEVER SQLERROR GOTO UNDO;
SET TRANSACTION
READ WRITE
ISOLATION LEVEL SERIALIZABLE;
INSERT INTO EMPLOYEE (FNAME, LNAME, SSN, DNO, SALARY) VALUES
(‘Robert’, ‘Smith’, ‘991001234’, 2, 35000);
UPDATE EMPLOYEE SET SALARY = SALARY * 1.1 WHERE DNO = 2;
COMMIT;
GOTO THE_END;
UNDO: EXEC SQL ROOBACK;
THE_END:
Database Management Systems II,
Hyunja Lee
15