| Simultaneous UPDATE/SELECT, is this possible [message #292087] |
Mon, 07 January 2008 14:06  |
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  |
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 #292093 is a reply to message #292090] |
Mon, 07 January 2008 15:07 |
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 |
rleishman
Messages: 3728
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 |
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
