The answer depends on the setting on ISOLATION LEVEL - with the default setting read committed

every query executed by a transaction sees only data committed before the query began

This means in your case if two different sessions perform this insert in parallel

-- session 1
insert into book (id,name,cnt) values (2,'a',(select max(cnt)+1 from book));

-- session 2
insert into book (id,name,cnt) values (3,'b',(select max(cnt)+1 from book));

you'll see (after both sessions commit) the same count in both new records.

        ID NAME              CNT
---------- ---------- ----------
         1 i                   1 
         2 a                   2 
         3 b                   2

Anyway if you do this on a columns with a unique constraint (e.g. primary key) - which you should NOT do at any circumstances! the behaviour is different (use sequence to assing ID for concurent insert - the max(id) +1 approch is OK for strictly single session run).

-- session 1
insert into book (id,name,cnt) values ((select max(id)+1 from book),'a',1);

-- session2 
insert into book (id,name) values ((select max(id)+1 from book),'b');

here the session 2 is waiting on a lock until the session 1 is commited and then you receive an error:

 ORA-00001: unique constraint (REPORTER.SYS_C0026759) violated

so only the first insert succeeds.

So the short answer ist no there are no inconsistencies. If you do not want to see identical count in more records you could catch it defining a unique constraint on the CNT column. But againg do not do this - the CNT column in the table is ill designed. The value should not be stored, but calculated in query (e.g. as a count of records with IDlower than the records ID).

作者: Marmite Bomber 发布者: 27.12.2017 05:33

I'm not sure what you mean by "safe". The query you proposed:

insert into book (id, name, count)
values(book_id_seq.nextval , 'stack', (select count(*) from book)+1);

will run successfully in an environment where multiple connections to the database execute the same statement concurrently but may not give the results you'd like, although I must confess I don't know what results you're expecting.

Let's consider the following example:

• At time one there are no entries in BOOK and BOOK_ID_SEQ's "Next number" is set to 1.

• At time two client A executes the above statement, creating the following row:

  ID=1 NAME='stack' count=1

• At time three client B executes the above statement, creating the following row (note that COUNT is 1 here because client A has not committed, and therefore client B cannot 'see' the row which has been created by client A yet):

  ID=2 NAME='stack' COUNT=1

• At time four client A commits changes.

• At time five client B commits changes.

• At time six client C executes the above statement, creating the following row:

  ID=3 NAME='stack' COUNT=3

• At time seven client C commits changes.

So our BOOK table now contains

ID    NAME    COUNT
1     stack   1
2     stack   1
3     stack   3

The actual timing of the statements doesn't matter - the time between the statement executions and commits could be microseconds or hours. What does matter is the sequencing of the statement execution and the commits.

Best of luck.

作者: Bob Jarvis 发布者: 27.12.2017 05:35