how INSERT works before issuing a COMMIT 7

[rivi10]

Hi,

My question is how oracle treats an INSERT transaction before issuing a COMMIT.

While I am doing an INSERT transaction, will oracle wait until I have inserted all my records within that procedure and then when I issue a COMMIT statement will the records be saved in a sequence for this transaction?

In the following code, the first insert that is made is the number of rows (metadata) and then the cursor loops and starts inserting the actual data.

Is there a possibility, in one transaction when I call this procedure, first my metadata record is inserted and then some other data (not related to this transaction) be inserted and then rest of my data. So that, the first record and the rest of the records from the loop are not inserted in a Sequence.

-- This code belongs to proecdure when ever a user clicks on insert
--button from the front end form

DECLARE

rowcnt NUMBER;

CURSOR c_get_employ IS
SELECT EMP.EMPLOYER_ID, EMP.EMPLOYER_NAME, EMP.EMPLOYER_LOCATION
FROM EMP
WHERE EMP.EMPLOYER_COUNTRY = 'USA'
ORDER BY EMP.EMPLOYER_ID;

BEGIN

Select count(*)
INTO rowcnt
FROM EMP
WHERE EMP.EMPLOYER_COUNTRY = 'USA'
ORDER BY EMP.EMPLOYER_ID;

-- I want to insert the 'number of employee records' that will be inserted (metadata)

INSERT INTO EMP_OUTPUT
(EMPID, EMPNAME, EMPLOC, ECOUNT)
VALUES
(,,,rowcnt);

-- Then loop through the cursor and start inserting the data
FOR c_post_employ IN c_get_employ

LOOP

INSERT INTO EMP_OUTPUT
(EMPID, EMPNAME, EMPLOC)
VALUES
(c_post_employ.EMPLOYER_ID,c_post_employ.EMPLOYER_NAME,c_post_employ.EMPLOYER_LOCATION);

END LOOP;

COMMIT;

END;

 

[ggriffit]

Your data is changed when you generate the INSERT, it is however only committed to the database, and thus available to all others user whenever a COMMIT is reached.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[rivi10]

Yes Greg. I am aware only on Commit it will be available for other users. And what do you mean by data is changed when I generate the INSERT?

My question is more on the sequence of how it is saved in the database.

 

[rivi10]

Ok..the scenario is very simple...........I do not commit after the first insert (as indicated in my code) and I still do not commit until I have looped through the cursor and inserted rest of the data. Only when I have no more records to insert..then I issue Commit.

 

[ggriffit]

You are correct, all the data will be inserted into your session by the INSERTs and then you commit which will save your changes and make them available in their entirety to other users.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[carp]

If I understand your question, you are wondering if concurrent sessions are doing inserts simultaneously, can some of session x's records be interspersed in the table with some of session y's records. The short answer is yes.

 

[Dagon]

I don't really like the tone of your question. It implies that you want records to be inserted in a particular sequence on the assumption that they will be stored in the database in that sequence. Presumably this means that you expect any queries to retrieve them in that sequence. This is very bad practice and I would re-think your design completely if you are relying on assumptions of that kind.

 

[carp]

Dagon -
Like yourself, I originally assumed there was a hope that rows would be stored contiguously - which, of course, is not always going to happen, even if there are no concurrent transactions occurring.

Then I thought it possible that rivi10 is concerned about the numbers being generated by a sequence and wanting them to be monotonically consecutive in a report. If this is the case, then rivi10 is still out of luck if there are concurrent transactions and will, as you point out, need to rethink the design.

 

[SantaMufasa]

My question is more on the sequence of how it is saved in the database.

You are correct, all the data will be inserted into your session by the INSERTs and then you commit which will save your changes and make them available in their entirety to other users.

I wish to clarify a statement that Greg makes, above. When Greg says "data will be inserted into your session by the INSERTs and then you commit which will save your changes...", Oracle does not save data-manipulation changes to a session somehow. When a session makes INSERTs, UPDATEs, or DELETEs against data, Oracle follows this process:[ul][li]Identifies which single rollback segment Oracle will use for this transaction[/li][/ul][ul]Then for each row that is changing:[li]Obtains a lock for the changing row(s).[/li][li]Copies to the rollback segment a pre-change image of the changing row.[/li][li]Makes the change to the row (within its block) in the database buffer cache in the System Global Area. In other words, the Oracle server that is handling the changes actually changes the database contents prior to the commit. When the user performs a COMMIT, the only additional "work" that must occur is to release the lock on the row. If the user decides to ROLLBACK, then additional work must occur in the form of copying the original image from the rollback segment back into the block in the database buffer cache.[/li][/ul]

If, during the transaction, some other user wishes to access a changing row of data (i.e., there is a lock on the row), then Oracle re-directs the other session's server to obtain the image for the locked row from the image in the rollback segment.



Regarding the physical order of rows in a table, newly inserted rows will go into the first physical location that appears on the "free list" for the table that is a large enough opening to accommodate the new row. The only way to assure that rows physically reside in a particular order in a table during initial insert into a completely empty table. Once an existing table has DELETEs (and UPDATEs), then the order of newly inserted rows becomes unpredictable.

Let us know if you need additional clarification.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[ggriffit]

Dave,
Thanks for expanding and clarifying my comments.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[rivi10]

Thanks to Greg, Dave, Dagon and Carp.

My bad..I was not clear enough...

The table I am getting the data from is pretty much static (that is the data does not get modified/updated when I am fetching). And the table to which I am going to insert, is a staging table from which websphere picks the data.

So, there may be other non related data being inserted into the staging table but the data is completely independent of each other.

I do not issue a commit until, I am done inserting all the records in a transaction.

Even in this scenario, will the insert records from transaction A be interspersed in the staging table with some of transaction B records?

 

[Dagon]

I would say that the word "interspersed" is meaningless here. If the other transaction commits before your transaction commits then, yes, you would have two lots of data at that point and would need to distinguish between them. The concept of sequence doesn't exist because Oracle never guarantees to store or retrieve records in any particular sequence. If you want to force some sequence onto your data, then you would have to do that yourself using an incrementing variable or an Oracle sequence.

One way around your problems may be to use a global temporary table for the staging table rather than an ordinary table. Problems of seeing other people's data wouldn't then exist because each session would only see the data it had inserted. However, it would depend on how long you want to keep the data in the staging table. Data in a global temp table lasts until either commit or the session end, so if you're able to do the whole process during a single session, that may be a good solution.

 

[Beilstwh]

a global temp won't work for a staging table. The other database grabbing from the table will be another session. What I would do is store a single value from an oracle sequence for all the rows that combine one transaction that way they would be grouped together.

Bill
Lead Application Developer
New York State, USA