Oracle programming Art Study Notes (14)-write consistency

 

Two concepts are explained first:
· Consistent read: the row to be modified is the Consistent read.
· Current read: Get the block to actually update the row to be modified. The obtained result is the Current read.
The tkprof report shows the statistics of consistent read and current read for each statement.
Tkprof: http://www.bkjia.com/database/201110/108829.html

Use an example to solve the problem. If you run the following statement
Update t set x = x + 1 where y = 5;
When this statement is run, other sessions update the row that has been read by this statement from Y = 5 to Y = 10 and submit it. If so, what will happen?

View plaincopy to clipboardprint? Time session 1 Session 2 comments
Bytes -----------------------------------------------------------------------------------------
T1 Update t this will Update the row matching the condition
Set y = 10
Where y = 5;
T2 Update t uses consistent read. This will find the record modified by session 1, but this record cannot be updated,
Set x = x + 1 because Session 1 has blocked it. Session 2 will be blocked and wait for this row to be available
Where y = 5;
T3 commit; this unblocks Session 2. Session 2 can finally include this line
(The row in which Y is equal to 5 when Session 1 is updated.
The current read found that Y is now equal to 10, not 5
Bytes -----------------------------------------------------------------------------------------
Time session 1 Session 2 comments
Bytes -----------------------------------------------------------------------------------------
T1 Update t this will Update the row matching the condition
Set y = 10
Where y = 5;
T2 Update t uses consistent read. This will find the record modified by session 1, but this record cannot be updated,
Set x = x + 1 because Session 1 has blocked it. Session 2 will be blocked and wait for this row to be available
Where y = 5;
T3 commit; this unblocks Session 2. Session 2 can finally include this line
(The row in which Y is equal to 5 when Session 1 is updated.
The current read found that Y is now equal to 10, not 5
Bytes -----------------------------------------------------------------------------------------

In this case, if you use the SERIALIZABLE isolation level, this transaction will receive a ORA-08177: can't serialize access error.
If the read committed mode is used, the transaction rolls back your update and restarts the update. Oracle enters the select for update mode and tries to lock all Y = 5 rows FOR your session. Once the lock is completed, it will run UPDATE on the locked data to ensure that this time can be completed without restarting.
However, if you restart the UPDATE and enter the select for update mode (like UPDATE, read-consistent block get also exists) when performing the select for update consistent read with the current block get (read current block get), a row of Y = 5 is found to be Y = 11 when you get the current version, what will happen? Select for update restarts, And the loop is repeated.

Create a before update for each row trigger on table T and print the output in the trigger. For example,
First, prepare the data:
Create table t (x int, y int );
Insert into t values (1, 1 );
Commit;

Then create a trigger:
Create or replace trigger t_print
Before update on t for each row
Begin
Dbms_output.put_line ('old. x = '|: old. x |', old. y = '|: old. y );
Dbms_output.put_line ('new. x = '|: new. x |', new. y = '|: new. y );
End;
/

Run in session 1:
Update t set x = x + 1 where x> 0;

Also run in session 2:
Update t set x = x + 1 where x> 0;
In this case, Session 2 will be blocked,

Return to session 1 for submission:
Commit;

In Session 2, we can see the output:
Old. x = 1, old. y = 1
New. x = 2, new. y = 1
Old. x = 2, old. y = 1
New. x = 3, new. y = 1

The trigger is triggered twice. We can see that a restart has occurred.

Interestingly
Update t set x = x + 1 where x> 0;
Replace statement
Update t set x = x + 1 where y> 0;
The same result is still displayed. Now we are searching for Y> 0 and haven't modified Y at all. Why is the update restarted?
It turns out that when the trigger references NEW. X and OLD. X, the consistent read value of X is compared with the current read value, and the two are different. This will bring about a restart.

Now re-create the trigger:
Create or replace trigger t_print
Before update on t for each row
Begin
Dbms_output.put_line ('fired ');
End;
/
Then run the update t set x = x + 1 where y> 0 statement to perform the test again. We can see that the trigger will only trigger once.
(Therefore, using an after for each row trigger is more efficient than using before for each row. The AFTER trigger does not have the above issue of update restart caused by the trigger .)

If you do any non-transactional work in the trigger, it will be affected by the restart. Consider the following impact:
· Consider a trigger that maintains some PL/SQL global variables, such as the number of processed items. When a restart statement is rolled back, modifications to PL/SQL variables are not rolled back.
· It is generally believed that almost all functions starting with UTL _ (such as UTL_FILE, UTL_HTTP, and UTL_SMTP) will be affected by statement restart. When the statement is restarted, UTL_FILE does not "cancel" The write operation on the written file.
· If the trigger is used as an autonomous transaction, the autonomous transaction cannot be rolled back when the statement is restarted and rolled back.
The conclusion is that do not do any non-transactional work in the trigger.

 

Author: NowOrNever