0

Consider these two transactions:

INSERT INTO foo VALUES (1, 2, 'bar');
INSERT INTO foo VALUES (1, 4, 'xyz');
COMMIT;

and

SELECT * FROM foo;

Is there any point in time when the SELECT would see only one row inserted from the first transaction?

So far I couldn't find any evidence that the data are visible only after the COMMIT is successfully finished. As Oracle writes the Redo log during commit, it writes it in a serial fashion, am I right? So there is a point where first row is written, but not the second one. And since writers do not block readers in Oracle, if the select hits exactly this window, then it sees only one row. Or is there some other locking mechanism?

APC
  • 144,005
  • 19
  • 170
  • 281
Jakub Zaverka
  • 8,816
  • 3
  • 32
  • 48
  • 1
    I'm not an expert but... Isn't that [configurable](https://blogs.oracle.com/oraclemagazine/on-transaction-isolation-levels)? – Álvaro González Oct 11 '19 at 11:13
  • 1
    @ÁlvaroGonzález - Oracle only supports READ COMMITTED and SERIALIZABLE isolation levels. Neither of those would support one session seeing an uncommitted transaction or a part of a transaction from another session. – APC Oct 11 '19 at 11:41
  • 2
    It depends on who the "reader" is. A different session from the one doing the inserting can only see the committed transactions. But the writer himself can read a single row inserted in the table: after the first INSERT statement but before the second one. –  Oct 11 '19 at 12:11
  • 1
    Also, you are using incorrect terminology. Each INSERT is a statement; but the *transaction* consists of **both** INSERT statements, if you only issue COMMIT after the second one. –  Oct 11 '19 at 12:17

2 Answers2

4

No. The data will not exist until the commit has been successful.

see ATOMICITY

Of course in the same session you can see the uncommited data e.g:

INSERT INTO foo VALUES (1, 2, 'bar');
SELECT * FROM foo;
INSERT INTO foo VALUES (1, 4, 'xyz');
COMMIT;

The select will show the inserted data even though the commit has not yet executed.

Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
  • But what does a successful commit consist of in Oracle? Since writes do not block readers, is there a mechanism that prevents readers from reading committed transaction as it is being written? – Jakub Zaverka Oct 11 '19 at 11:32
  • You don't need atomic writes for records. To have transaction atomicity you need only atomic write to the transaction status. – Alexey Vlasov Oct 11 '19 at 11:35
2

Nope. It's impossible to see just one row.

I don't have exact implemenation details but the main idea is every record has associated last modified transaction number. When other transaction reads data it checks the status of the last modified record transaction (and their own isolation level) and fetches only allowed records. (This is a pretty common for any MVCC databases)

Moreover even when fetching transaction has RC isolation level each query before execution makes a snapshot of currently active transaction statuses and uses it to perform check above. It actually means that every query runs in SNAPSHOT isolation level. (This is oracle specific feature)

More details here: https://docs.oracle.com/cd/E25054_01/server.1111/e25789/consist.htm

Check the multiversion read and the statement level read consistency parts.

Alexey Vlasov
  • 355
  • 2
  • 5