Log into a MySQL session: SET TRANSACTION ISOLATION LEVEL < isolationlevel >; ST

Question

Log into a MySQL session:
SET TRANSACTION ISOLATION LEVEL < isolationlevel >;
START TRANSACTION;
SELECT * from < table >
UPDATE/INSERT/DELETE ....
finally COMMIT or ROLLBACK to end transaction.
If you do not start a transaction then each individual query is treated as a separate transaction.
Alternatively, you could turn autocommit off. Note that isolation levels are relevant only for reads
from database; all writes to the database require exclusive locks.

(Q) Consider the following transaction on table R(a).
T1:
update R set a = 2*a;
Commit
T2:
update R set a = 2+a;
Commit
T3:
select a from R;
select a from R;
Assume that each query executes atomically. Let R have value (1) before the start of trans-
action. Give all possible outputs of T3 when
(a) T3 execute with isolation level Serializable:
(b) T3 execute with isolation level Read Committed. List only those outputs that do not
overlap with Serializable.
(c) T3 executes with MySQL’s isolation level Repeatable Read. List only those outputs
that do not overlap with Serializable.

Explanation / Answer

A) serializable returns results after completion of the earlier transactions so the last query result will be returned and value is 2+1= 3

B) read comitted reads the value after execution of commited transaction and the value which doesnot overlap with serializable is 2*1=2

C) mysql isolation level is also same as above and value is 2*1=2

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.