0

I wondered if there is anyway to avoid corruption of data in READ COMMITTED isolation level.

Here is a sample of my issue: two sessions working with the same tables. 

SSN1> ALTER TABLE APPLICANT ADD( AVGSLEVEL2 NUMBER(5,2) )

Meanwhile in another session....

SSN2> INSERT INTO SPossessed VALUES ( 000001, 'TRUCK DRIVING', 9 );

Back in the first session ...

 SSN1> UPDATE APPLICANT 
    2  SET AVGSLEVEL = ( ( SELECT SUM(SKILLLEVEL) 
    3                      FROM SPOSSESSED
    4                      WHERE A# = APPLICANT.A# ) /
    5                    ( SELECT COUNT(*)
    6                      FROM SPOSSESSED
    7                      WHERE A# = APPLICANT.A#) );

Then second session does ...

SSQN2> select AVGSLEVEL from APPLICANT;

But when first session issues a commit ...

SSN1> COMMIT;

... then what does second session get?

SSN2> select AVGSLEVEL from APPLICANT; 
SSN2> COMMIT;

how to improve first session SQL script included such that it can be safely processed at READ COMMITTED isolation level?

APC
  • 144,005
  • 19
  • 170
  • 281
Bernard
  • 4,240
  • 18
  • 55
  • 88
  • You haven't really explained what issue you have with this. What isn't safe about it? The right side only sees the data it inserted; the left side doesn't update that data. What corruption are you seeing? – Alex Poole Jun 01 '14 at 18:41

1 Answers1

0

In a multi-user environment we must be aware that individual users can confuse things with their uncoordinated actions.

The problem isn;t READ COMMITTED. It's perfectly sensible that the UPDATE in SSN1 doesn't take account of the TRUCK DRIVING skill because the second session hasn't committed yet. The user could roll back, or the transaction could fail for some other reason. So, at the time the user in the first session executes their update truck driving does not exist as a possessable skill.

That is completely safe. How could it be safer?

Now, you could check for uncommitted transactions, by serializing things. Before user #1 executes the update they could issue a LOCK TABLE SPOSSESSED statement. That would fail, because SSN2 has a uncommitted transaction. Or, if issued early enough, would prevent SSN2 from doing the insert until SSN1 had committed the update.

But, SSN2 cab subsequently insert the Truck Driving record, so APPLICANT.AVGSLEVEL would still be out of whack. There's nothing you can do about that ...

... except build a transactional API which enforces business rules and prevents random or spontaneous amendment of data in business tables.

APC
  • 144,005
  • 19
  • 170
  • 281