Home » SQL & PL/SQL » SQL & PL/SQL » Simultaneous UPDATE/SELECT, is this possible
Simultaneous UPDATE/SELECT, is this possible [message #292087] Mon, 07 January 2008 14:06 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
We have had a strange happening and we have a theoretical explanation for it, but it seems so outlandish that I just needed to throw this up and see if this sounds even remotely plausible to others.

We run a Financial system one of the functions of which is to track employee time, every two weeks we receive a file from another system that details the employees vacation, personal, sick, etc hours availible to discharge.

There is a timing issue with the file so that the balances in the file do not reflect time discharged for the past two weeks so to load the file with look for a total of discharged hours for the employee counting hours as discharged once the time-card is submitted.

On the other side of this we have our users who can create and save time-cards. When they submit a time-card work-flow is used to subtract the discharged hours from the employee's balances.

So with that set-up, we had an employee who submitted a time-card as the update process was running, the following week the employee was able to submit a second time-card re-using the hours that had been discharged the previous week.

Our theory is this:

The employee submitted the time-card after the balances which need to be subtracted had already been read, but before the new balances had been written to the system. So the events would go something like

1. Load process reads employee discharged hours for previous two weeks.
2. Employee submits time-card which adjusts the discharged hours.
3. Process updates the balance tables using the discharge info from prior to the submission in essence removing any impact the submission had on employee leave.

Why this theory seems completely and totally crazy:

The load process is for the purpose of this discussion two cursors one which reads every row loaded from the balances, and a second which is run for each employee as their record is being processed to get the discharged balances. So the difference in time between discharged hours being read and the update happening is very very small so the sequence of events would have to go something like this:

1. Update process starts processing
2. Update process gets to target record and calculates discharge values.
3. Time-card submit alters balances table.
4. Update process writes new value to balance table which ignores the recent submission.

Does this seem in any way plausible? We do know that the submission did happen between the start and end times of the process so that supports this theory it just seems way to hard to believe that the timing could match up that well.

Andrew
Re: Simultaneous UPDATE/SELECT, is this possible [message #292088 is a reply to message #292087] Mon, 07 January 2008 14:12 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You could try simulating this to see.

On a development instance, put a dbms_sleep.lock between the two cursors, have someone do a time card update, then see if the results are what you experienced in your production environment.

Just because something is unlikely doesn't mean it will not occur.

Look at the odds of hitting the lottery or a royal flush, yet it happens.

[Updated on: Mon, 07 January 2008 14:16]

Report message to a moderator

Re: Simultaneous UPDATE/SELECT, is this possible [message #292089 is a reply to message #292088] Mon, 07 January 2008 14:14 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Good idea, I have been trying to figure out a way to test and that aught to work.

Andrew
Re: Simultaneous UPDATE/SELECT, is this possible [message #292090 is a reply to message #292087] Mon, 07 January 2008 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It is NOT clear to me the timings involved.
Keep in mind that Oracle will ALWAYS presents a read consistent view of the data.
Once Cursor_A starts processing, it will not "see" changes made by Cursor_B which starts after Cursor_A begins.
Changes made by Cursor_B will not be visible to other sessions until after a COMMIT is issued & only by sessions that start after the COMMIT is done.
Re: Simultaneous UPDATE/SELECT, is this possible [message #292093 is a reply to message #292090] Mon, 07 January 2008 15:07 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
anacedent wrote on Mon, 07 January 2008 14:17

only by sessions that start after the COMMIT is done.



I do not believe this to be correct. The commit will be visible to all sessions regardless of when the session was created, it will not be reflected in open cursors but in a specific session you will still be ok.

TEST

1. Open two toad tabs (assuming you do session per tab)
2. Read value in tab 1 and 2 should match.
3. Perform update in tab 1
4. Read again in tab 1 and 2 will not match
5. Commit in tab 1
6. Read in tab 1 and 2 will match again.

Though does this change because our main cursor never closes? I am trying a test to see if that is the case now and I think I am locked up...

That being said I think we did miss something...went back to the person who runs this process for some more info and I was mistaken in one point of my first description and I think there might be another issue here.

The actual balance update process is this:

1. Main load cursor opens which will be loading our balance info from an external source
2. For each record in the main load cursor some basic validation is done basically checking to see if the person it refers to is a valid person.
3. Once validations are performed the persons original balance record is DELETED.
4. Missing discharged cursor runs which will look for leave based expenditures which must be added back into our balance value
5. New record is inserted into the balance table which reflects load from the file minus any selected discharges.
6. Finally after the process runs for all employees a commit is performed.

Now the submission process is still the same for the sake or argument here it will basically consist of two items.

1. Insert of expense items which are what is checked by the load process looking for discharged hours.
2. An UPDATE to the balance table based on a person ID value which will remain constant before and after the load process.

With this redefine I can't figure out a series of timing that get the whole thing to work.

Did a test and if I do a
1. DELETE in session 1,
2. UPDATE of same record (based on a ID value not a rowID) in session 2
3. UPDATE blocks
4. INSERT new record with a matching ID value in session 1
4. COMMIT session 1
5. UPDATE block will release and the newly created record is updated

I think maybe I need to look a bit more closely at the ordering of things going on in the submission process to see if perhaps the different pieces of information get committed separately. Will post back once I take a closer look at that stage so I can get the details of what happens when there.

Andrew
Re: Simultaneous UPDATE/SELECT, is this possible [message #292108 is a reply to message #292093] Mon, 07 January 2008 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I agree with anacedent. It's not about when you commit, it's about read-consistency.

Say you open a cursor at 08:00 that fetches row-by-row - updating information as it goes - and doesn't fetch the last row until 14:00 (6 hours later).

If the cursor does not have FOR UPDATE, the last row will not be fetched until 14:00, but the image of data that the cursor receives will be as at 08:00. This means that there is 6 hours of opportunity for any user to update the row between 08:00 and 14:00, which would result in a prior image being processed by the cursor.

If you add a FOR UPDATE clause to the cursor, you will fix the problem because the cursor will pre-lock all rows, however you will be unable to perform interim commits.

Ross Leishman
Re: Simultaneous UPDATE/SELECT, is this possible [message #292280 is a reply to message #292108] Tue, 08 January 2008 07:05 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
rleishman wrote on Mon, 07 January 2008 20:59

I agree with anacedent. It's not about when you commit, it's about read-consistency.

Say you open a cursor at 08:00 that fetches row-by-row - updating information as it goes - and doesn't fetch the last row until 14:00 (6 hours later).

If the cursor does not have FOR UPDATE, the last row will not be fetched until 14:00, but the image of data that the cursor receives will be as at 08:00. This means that there is 6 hours of opportunity for any user to update the row between 08:00 and 14:00, which would result in a prior image being processed by the cursor.

If you add a FOR UPDATE clause to the cursor, you will fix the problem because the cursor will pre-lock all rows, however you will be unable to perform interim commits.

Ross Leishman



In this case though we have two cursors and only one is open the entire time, the second cursor is opened once for every row in the main cursor. Will this second cursor be subject to the same rules because the main cursor is never closed?

Basically is the read image based on just single cursors or is it session wide?
Previous Topic: Help on Group subquery
Next Topic: Help in pivot query
Goto Forum:
  


Current Time: Thu Dec 08 22:20:01 CST 2016

Total time taken to generate the page: 0.08817 seconds