Locking table for inserting a key

[Lionel_F]

Hello,

I have 2 batches that want to update the same table (and the primary key within), but if they run at the same time, one doesn't see the update of the other, and tries to insert again the same key , although the code checks for the PK ( through a "not in" clause")

It seems that there is no lock on the pk, how could I set one (dynamically ?).

Thanks for your answers, if I'm not very clear please tell me
.

[Lionel_F]

To be more precise, when I run this, it works ok :
select STATUS_DT,FILE_ID,TVSEQ_ID
from FLASH_DATA_HIST
where STATUS_DT = trunc(sysdate)
and FILE_ID = 0
and TVSEQ_ID = 555
for update

but when trying to execute this one, Oracle 9i says 'Invalid relational operator" on the 'for update' clause.

select trunc(sysdate),0,555,'Text', '0000000','XX',trunc(sysdate),trunc(sysdate), null,'N','N',sysdate,'Text2',null
from dual
where (trunc(sysdate),0, 555) not in (
select STATUS_DT,FILE_ID,TVSEQ_ID
from FLASH_DATA_HIST
where STATUS_DT = trunc(sysdate)
and FILE_ID = 0
and TVSEQ_ID = 555
for update )

[nandeep]

Hi Lionel

Well just registered yesterday, so the delay in the reply.

Hopefully you already have the solution. However I post it here for the benefit of other user who might find it useful:

Your query has a "FOR UPDATE " clause in the Sub-query.

Quoting from the Oracle manuals
"The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. ~~~You can specify this clause only in a top-level SELECT statement (not in subqueries)~~~. "

So your query has to be .
"select trunc(sysdate),0,555,'Text', '0000000','XX',trunc(sysdate),trunc(sysdate), null,'N','N',sysdate,'Text2',null
from dual
where (trunc(sysdate),0, 555) not in (
select STATUS_DT,FILE_ID,TVSEQ_ID
from FLASH_DATA_HIST
where STATUS_DT = trunc(sysdate)
and FILE_ID = 0
and TVSEQ_ID = 555
)
FOR UPDATE.
"

Hope this has answeredd your question.

-nvn
---------------------------

Originally posted by Lionel_F
To be more precise, when I run this, it works ok :
select STATUS_DT,FILE_ID,TVSEQ_ID
from FLASH_DATA_HIST
where STATUS_DT = trunc(sysdate)
and FILE_ID = 0
and TVSEQ_ID = 555
for update

but when trying to execute this one, Oracle 9i says 'Invalid relational operator" on the 'for update' clause.

select trunc(sysdate),0,555,'Text', '0000000','XX',trunc(sysdate),trunc(sysdate), null,'N','N',sysdate,'Text2',null
from dual
where (trunc(sysdate),0, 555) not in (
select STATUS_DT,FILE_ID,TVSEQ_ID
from FLASH_DATA_HIST
where STATUS_DT = trunc(sysdate)
and FILE_ID = 0
and TVSEQ_ID = 555
for update )