This forum is currently locked. You can't register or post questions at this time. (read more)

Oracle concurrency and table data

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

cdummy
Member
Posts: 2
Joined: Thu May 15, 2014 1:31 pm

Oracle concurrency and table data

Postby cdummy » Thu May 15, 2014 1:39 pm

Consider i am offering a lottery for all those who enroll themselves with thier friends using sql*plus before 12 o clock. There is no column to store the timestamp say just a list of names in each row. A person m1 inserted his ticket already before 11:59 and commited.

He starts updating his (row) details at 11:59 adds his maid to his lottery list (only needs to commit). Another person m2 running insert statement starts his first insertion (for himself alone) at 11:59 (only needs to commit). Its a huge table with 20 million rows.

Its 12 now. Consider this as read committed isolation level.

The lottery batch started, moving registered users to some other tables. After that ( very possible next nano second), both m1 and m2 commits.

Will my select statement in batch add m1 with m1's maid and m2 to the lottery winners list or not?

Consider scn(system change number), phantoms and non-repeatable reads while answering.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle concurrency and table data

Postby Tim... » Thu May 15, 2014 1:57 pm

Hi.

When you query Oracle, you will *only* see committed data from other sessions, along with any uncommitted changes from the current session. The results of the query will be consistent to the point in time when the cursor (SELECT or DML) was opened. Any changes that were committed after the cursor was opened will not be seen...

Looking at your example, M1 will be present in the winners list, but M1's maid and M2 will not be present.

The size of the table is irrelevant to this process. Oracle does not alter the approach for different size tables...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

cdummy
Member
Posts: 2
Joined: Thu May 15, 2014 1:31 pm

Re: Oracle concurrency and table data

Postby cdummy » Thu May 15, 2014 2:28 pm

Hi Tim,

Thanks. please explain the role of SCN in this? Figure 13-1 Transactions and Read Consistency http://docs.oracle.com/cd/B19306_01/ser ... onsist.htm.
The m2 block will have much recent SCN however it cant find any rollback for that. So how oracle decide over this issue? –

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle concurrency and table data

Postby Tim... » Fri May 16, 2014 9:09 am

Hi.

When the cursor is opened, all data is made read-consistent with the point-in-time. Whenever you hear point-in-time, you are actually talking about a specific SCN. The way the database does this is by doing consistent gets. It reads the current data from the datafiles and applies undo to "wind it back" to the right point in time. This is not done with redo. It is done with undo...

When you perform any operation like flashback or recovery using a timestamp, the timestamp is converted to an SCN, so really, all PITR and flashback is SCN based really...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron