[discussion post] update statement to update oracle restart problem-CodePudding

The new table

-- -- -- -- -- the experiment 1

- the experiment 2

Experiment 2 contents of the insert statement is also updated!!!!!! Because the experiment 2 has to reset the update

Why didn't the experiment 1 to reset the experiment 2 has updated?

In experiment 1 session1 in time according to the current six time and read the value=8, and consistent read value=3, the current read and consistent is not equal, why not update the restart?

CodePudding user response:

Should not appear such problems

CodePudding user response:

You can try, the version is 11.2,

CodePudding user response:

Like this little serious study of post, recommend, welcome to discuss,

CodePudding user response:

Please, answer

CodePudding user response:

refer to the second floor quanjunchuji response:

, you can try version is 11.2, the

I really was tested under this kind of situation, don't understand why

CodePudding user response:

refer to the second floor quanjunchuji response:

, you can try version is 11.2, the

My test result is not restart will insert the updates

CodePudding user response:

There, don't session1 did cache, plus will refresh again after the where? Don't know how to design the oracle

CodePudding user response:

It would be odd if

CodePudding user response:

The only difference is that the two experiments session1 two update statement, one with a where clause, there is no

I put the two statements executed the memory dump for a change, from which it can be seen that the UGA and PGA, mainly lies in the SGA in the where clause executes a callback process, it is similar to the relationship between the main function and other functions in the program, but the main function and there are both the main function and function execution must be different, so it should be this step correction process affect the final result, don't know whether it is right, please expert analysis,

CodePudding user response:

Also can understand it, there is no where clause, the update will have a set of operations, namely full table data, and has a where clause, the update operation data sets from a full table, became a screening after the result set, and the formation of the two result set time is not the same,

CodePudding user response:

http://blog.itpub.net/28539951/viewspace-1705502/
This can well solve you this problem

CodePudding user response:

Like this little serious study of post, learning

CodePudding user response:

To understand this problem, you need to understand the transaction isolation level, the level of your lab should be read commit
At this level, the oracle at the time of query get all the data has a commit, so the results it is understandable,

Two results is inconsistent inside your experiment & gt; This option causes data is 0.5 2 screen off, not two do not match the experimental results,
Judging from the results, in fact the two experimental results are consistent, is proved that the oracle transaction isolation level,

CodePudding user response:

I looked at the host's questions very carefully, I found my previous reply is problematic, I'm very sorry, hope not mislead you, in principle explain it now, you the causes of this problem,
Because the basic principle of design, oracle, I hope you have the patience to see,

Oracle on transaction control, there are two basic concepts, that is read the commit and multiversion version (null),
Read the commit isolation level determines the oracle transaction only after submitting, other session to see transaction changes to the data, but this isolation
Read many times within a level cannot ensure the transaction is consistent, that is, the so-called phantom reads will be
Version control, ensure that the single read is consistent, understand the need to study some basic principle, this is not much said,

Then is the basic principle of the update, the update without conditions is a read twice conditional update is
Experiment 1: because the updat no conditions, it is a time to read, so the data version is the beginning of the statement, so 0.5 is not updated,
Experiment 2: because there are conditions, the update is the second reading, so data version is clogged lift, phantom read that is to say, so the 0.5 update into 0.6

CodePudding user response:

You this picture easy to mislead the people, lock wait will happen anyway, 2 up to 1, 1 performed before 3

CodePudding user response:

Positive learning solution? The query SELECT

http://www.verejava.com/? Id=17173779389953

CodePudding user response:

Produced a phantom read, with the highest transaction isolation level (serialization) will not happen

CodePudding user response:

Installed, but I don't know will always appear some problems

CodePudding user response:

My unprofessional, extreme, perform the experiment at the moment, can think is id in (1, 2, 3), the experiment 2 can think is a condition for the map, when mapping changes, condition change, in an experiment to join id=4 will it change, look forward to

CodePudding user response:

Test negative,

Should be the condition and related, but what is the principle still don't understand!

CodePudding user response:

This kind of situation in the program should be not allowed to produce

CodePudding user response:

Didn't even see

CodePudding user response:

When the UPDATE operation contains the WHERE condition, then this should satisfy the WHERE the results of the query definition of query consistency, when UPDATE found consistency does not meet the conditions, will trigger the UPDATE restart,
For does not contain the WHERE condition or contains the WHERE condition but the condition has nothing to do with the table query, UPDATE to focus solely on the data of the UPDATE statement issued a moment, no longer consider overall the results of the updated consistency issues,

CodePudding user response:

This is the oracle transaction isolation caused by oracle default transaction isolation level is read, that is to say, only committed data to read

CodePudding user response:

Learning, the company of everyone

CodePudding user response:

Session2 the statement did not commit written, so eligible rows are locked, the UPDATE is row locking, session1 statements of constraints, so the statement to access session2 in line, it is impossible to carry out, so you will be prompted session3 statement is insert statement, do not conflict with session1 of pin on the line, so it can perform

CodePudding user response:

The transaction isolation level

CodePudding user response:

Experiment 2 should be no problem, and the experiment 1 I think you may insert statement caused by not timely submitted, suggest to experiment 1, validation,

CodePudding user response:

I think you are the should have session3 insert statement execution at the session1.

CodePudding user response:

The problem of transaction isolation level

CodePudding user response:

You want to lock the ah session2 implementation,nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull