Isolation Levels — Series 2

Preview — 3 of 10 questions

What does BEGIN READ ONLY do, and what's a benefit?

javascript

BEGIN READ ONLY;
SELECT * FROM orders WHERE status = 'PENDING';
COMMIT;

AIt prevents any data-modifying statement (INSERT/UPDATE/DELETE) from succeeding within that transaction — attempting one raises an error; this documents intent clearly and, combined with certain settings, can allow some query optimizations

BREAD ONLY transactions execute twice as fast as normal transactions in all cases

CREAD ONLY prevents the transaction from ever being aborted by a deadlock

DREAD ONLY is required syntax for any transaction containing only SELECT statements

How do you set a default isolation level for all subsequent transactions in a session, instead of specifying it after every single BEGIN?

javascript

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
-- this transaction automatically uses REPEATABLE READ, without saying so explicitly
SELECT ...
COMMIT;

BEGIN;
-- this one too, and so on, for the rest of the session
COMMIT;

AALTER DATABASE SET default_isolation = 'REPEATABLE READ';

BSET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; — sets the default for every transaction started afterward in this session, until changed again or the session ends

CThere is no way to set a session-wide default; the isolation level must be specified after every BEGIN

DSET GLOBAL isolation_level = 'REPEATABLE READ';

What happens to savepoint sp2 if you RELEASE SAVEPOINT sp1, given sp2 was created after sp1?

javascript

BEGIN;
SAVEPOINT sp1;
INSERT INTO a VALUES (1);
SAVEPOINT sp2;
INSERT INTO a VALUES (2);
RELEASE SAVEPOINT sp1;

Asp2 remains usable independently — releasing sp1 has no effect on sp2

BRELEASE SAVEPOINT sp1 automatically commits the entire transaction

CReleasing an outer savepoint (sp1) also releases every savepoint created after it (sp2 included) — you can no longer ROLLBACK TO SAVEPOINT sp2 after this

DThis raises an error — you must release savepoints in the reverse order they were created, and sp2 must be released first

Answer all 10 questions (7 more), see explanations for every answer, and track your score.