|
View previous topic :: View next topic
|
| Author |
Message |
Renato Zangerolami
New User
Joined: 06 Dec 2019
Posts: 28
Location: Brazil
|
|
|
|
I have a problem when executing a select max when the transaction (IMS) is executed, that same transaction can be executed at the same time. after the select is made an insert.
Example:
Transaction 1
| Code: |
EXEC SQL MAX (FIELD)
INTO: XXXXX
FROM TABLE
WHERE ...... |
Transaction 2
| Code: |
EXEC SQL MAX (FIELD)
INTO: XXXXX
FROM TABLE
WHERE ...... |
both transactions are returning the same select MAX (FIELD) because they are running at the same time, so I'm having a problem with the insert because I have a duplicate key
Does anyone have any idea how to solve this?
Coded for you |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
I believe this is a general logical problem with ANY DATABASE SYSTEM, not only IMS DB/DC.
You need to provide any type of blocking all other conflicting transactions from running while the first of them has not finished. The method of blocking may be different for various databases, but the idea must be logically the same for any of them. |
|
| Back to top |
|
|
Renato Zangerolami
New User
Joined: 06 Dec 2019
Posts: 28
Location: Brazil
|
|
|
|
I tried to use some options like
- WITH RR USE AND KEEP EXCLUSIVE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP UPDATE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP SHARE LOCKS (WITH RR AND RS)
But it didn't work very well
the best option that worked was using
EXEC SQL LOCK TABLE XXXX IN EXCLUSIVE MODE END-EXEC.
this is the first time I use this command, my only concern is with the delay this may cause, as this transaction processes thousands of accounts per day ... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
Looks like a logical error.
Once you get the MAX( field) , you can simply check in DB if it exists and if yes then redo that MAX again to get latest value and do INSERT again.
OR ( This one is most preferred)
Explore if it supports transaction sequencing like we have in CICS by using ENQ and DEQ method which will work perfect and designed for such situations. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
AFAIK, in IMS DB/DC there is a command to perform such locking, at different levels:
LOCK DB
LOCK TRAN
UNLOCK DB
UNLOCK TRAN
etc. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Renato Zangerolami wrote: |
I tried to use some options like
- WITH RR USE AND KEEP EXCLUSIVE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP UPDATE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP SHARE LOCKS (WITH RR AND RS)
But it didn't work very well
the best option that worked was using
EXEC SQL LOCK TABLE XXXX IN EXCLUSIVE MODE END-EXEC.
this is the first time I use this command, my only concern is with the delay this may cause, as this transaction processes thousands of accounts per day ... |
This looks to be one of acceptable methods. Some delay is possible, but this is inevitable (to avoid failures, or wrong results). You can minimize the delay by making the “distance” between LOCK TABLE, and UNLOCK TABLE as short as possible: LOCK it just before SELECT MAX, and UNLOCK it immediately after INSERT new_timestamp |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
no forum advice will fix a bad application design  |
|
| Back to top |
|
|
|
|
