This
> Read does not block read.
> read does not block write.
> write does not block read.
> write blocks write on the same column.
should read:
Oracle has a row locking mechanism, so
the following blocking mechanisms apply, when two
or more oracle sessions want to operate on the same row:
read does not block read.
read does not block write.
write does not block read.
write blocks write.
So if two ppl write on the same row on the same time,
oracle waits for the first transaction, to be committed,
or rollbacked, then for the second...and so forth.
if you send your insert query with the select max(myrow)+1 form table;
and some other session inserts also with your statement at almost the
same time and commits in the meanwhile, that won't affect your max(myrow)
result in any way. oracle will bring you the result as it would have been
as you started your query.so it is for session b. the insert wont have to
wait, it
doesn't affect the same row as the other session. so you'll get the same
results.
open two sql plus windows.
in the first do:
> insert into acteursenc (nuacteur,nomacteur)
(select AA, BB from
(select max(nuacteur)+1 AA from acteursenc),
(select 'Michael Sweeney' BB from dual)
then in the second do:
> select max(nuacteur)+1 AA from acteursenc
then in the first
> commit
and in the second:
> select max(nuacteur)+1 AA from acteursenc
yo'll see, that AA will be 1 higher the second time
Michael
"Michael Virnstein" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> the problem is, that the second sql produces more
> than one row. To be exactly the amount of
> select count(*) from acteursenc rows, with
> 'Michael Sweeney' as value in BB. Perhaps you
> have a unique key on nomacteur?!
>
> I could help further, if i know what you want to do
> with your query.
> if nuacteur is a pk, use a sequence!!!!!
> if two users send this query almost at the same time,
> you'll get two times the same pk.
> That's because of oracles locking mechanism:
> Read does not block read.
> read does not block write.
> write does not block read.
> write blocks write on the same column.
> if someone inserts a row with your statement, and hasn't commited his
> transaction,
> and someone else inserts a row with your statment before he has commited,
> then the two will get the same results for max(id)+1. A sequence will
never
> give the
> same result and is easy to use. and for your query, wouldn' this be
easier:
> insert into acteursenc
> (nuacteur, nomacteur)
> values
> (S_ACTEURSENC.NEXTVAL, 'Michael Sweeney')
>
> please explain what you want to do.
>
> Michael
>
> "Michael Sweeney" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> > My following query :
> >
> > insert into acteursenc (nuacteur,nomacteur)
> > (select AA, BB from
> > (select max(nuacteur)+1 AA from acteursenc),
> > (select 'Michael Sweeney' BB from acteursenc))"
> >
> > produces an ORA-00001: unique constraint error.
> >
> > The primary key is nuacteur, but by setting AA to max(nuacteur)+1 I
should
> > be getting a new key that is unique, however it does not seem that way.
> >
> > What am I doing wrong here?
> >
> >
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
