MacLochlainns Weblog

I’m always amazed at how people ask questions about skill sets. A few weeks ago, I ran into a neat Oracle SQL question that hinges on whether the individual truly understands One Phase Commit (1PC) and Two Phase Commit (2PC) operations.

It’s a simple question that tests two levels of understanding. The question is:

• If you create table A and insert a row of data and subsequently you create table B and insert a row of data before issuing a ROLLBACK; statement, how many rows of data will you find in table A and table B?

Level 1 Understanding

Here’s the test script:

CREATE TABLE a1
( text  VARCHAR2(12) );
INSERT INTO a1 ( text ) VALUES ('Hello World!');
CREATE TABLE b1
( text  VARCHAR2(12) );
INSERT INTO b1 ( text ) VALUES ('Hello World!');
ROLLBACK;

The answer is 1 row in table A1 and no row in table B1 because the second CREATE statement issues an implicit COMMIT. However, the INSERT statement to table B1 is a 2PC and the ROLLBACK statement undoes the first phase of the INSERT statement and removes the data. If you were using a sequence value in the INSERT statement, the sequence value would be consumed because it’s not replaced by a ROLLBACK statement.

Level 2 Understanding

Here’s the test script:

CREATE TABLE a2 AS (SELECT 'Hello World!' AS text);
CREATE TABLE b2 AS (SELECT 'Hello World!' AS text);
ROLLBACK;

The answer is 1 row in table A2 and 1 row in table B2 because a CREATE statement using the AS clause subquery issues an implicit COMMIT on the data inserted from the subquery because its a 1PC transaction.

Most likely and interviewer would be looking for level one understanding but you can demonstrate mastery by sharing level two understanding. As always, I hope this helps those reading it.