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.

Installing Parallels on my iStudio (M2) was straightforward because I let it install Windows 11. Then, when I wanted to install Ubuntu it wasn’t quite that easy. You just need to understand that you click the Parallels’ Window menu option and Control Center option.

The Control Center option provides the following dialog.

Click the + symbol to create a new virtualization and you get the following dialog; and choose the Download Ubuntu with x86_64 emulation if you want to install a Docker image that’s not ported to ARM, like the Oracle Database 19c.

It’ll then explain in this dialog that is uses Apple’s Rosetta 2 technology, which means you should be able to install an Intel architecture Docker image with Oracle Database 23c.

As always, I hope this helps those trying to sort out how to leverage a new stack.