Jump to content


- - - - -

Simultaneous transactions


3 replies to this topic

#1 Guest_Guest_Sudhakar_*

Guest_Guest_Sudhakar_*
  • Guests

Posted 04 January 2006 - 04:35 AM

Hi Gang,

One of my module has oracle log file of all transactions in a particular accounting screen. This file will be added with transaction type as 'A' in case of addition and 'C' in case of change mode etc, when user confirm the screen.

This file has only one key field ie sequence number.

The logic behind addition is - >

LS11 - Add mode of file - IACTEMP1
Before read logic -
LSCALL(12)
IAC-SEQ = CONV(##LAST-SEQ + 1)

call to LS12, to get last sequence number in the table and increment it and assign to key field.


LS12 - Def logic -

SQL
SELECT FROM IACTEMP1
WHERE IAC_SEQ =
(SELECT MAX(IAC_SEQ) FROM IACTEMP1)
ENDSQL

The above is working fine. But it is failing when both users on the same screen and do confirm of the operation on same time.
One user is getting integrity constaint sql problem.
I suspect the problem - As both users are getting the same last sequence number and trying to add with same next number, this problem arised.

How to avoid this simultaneous users problem. Let me know the best action to be taken for this problem.

I tried to add RETRY_READ(), but not working.

How to use oracle sequence in proiv?

Thanks in Advance .....

Sudhakar Chunduri.

#2 Guest_Luke_*

Guest_Luke_*
  • Guests

Posted 04 January 2006 - 02:34 PM

I'm not an expert, but I would use an oracle seq rather than your own file, in pro-iv all you need is some inline SQL:

SQL
select seq_name.nextval
from dual
ENDSQL

where seq_name is the name of the oracle sequence you have set up.

this will stop the problem of 2 people getting the same sequence number.

#3 Luke

Luke

    Newbie

  • Members
  • Pip
  • 4 posts
  • Gender:Male

Posted 04 January 2006 - 02:42 PM

sorry, I should say

SQL
select seq_name.nextval
into :#variable
from dual
ENDSQL

#4 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 05 January 2006 - 09:44 AM

Just beware when it comes to Oracle Sequences. If you cache your sequence numbers to x number of sequences, if the database closes abnormally you lose those sequence numbers.

Have you tried assigning sequence numbers and committing that change immediately ?

Or make the sequence number your terminal no and you all your processing first. Once you finished processing, update your table with a sequence no.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users