View previous topic :: View next topic |
Author |
Message |
vijay Beginner
Joined: 09 May 2003 Posts: 131 Topics: 64
|
Posted: Wed May 16, 2007 7:44 am Post subject: Concurrent Update problem |
|
|
We've a cics module that is used by web for order creation.The cics module generates order no. (using max(orderno) + 1 say from t1 table)and inserts at the end of the transaction.The problem is when another using the same transaction at the same time,we get duplicate insert error.If we put a lock we get contention errors.
Any suggestions about fixing this?
Vijay |
|
Back to top |
|
 |
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Wed May 16, 2007 8:02 am Post subject: |
|
|
Use a different method to generate the order number, like identity column, which db2 will generate at time of insert. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
 |
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Wed May 16, 2007 8:26 am Post subject: |
|
|
I have thought about this for a few minutes (mainly to control my laughing!).
If you only have one task at a time, max(orderno) would work. As the days go by, you have more order numbers and the max function takes longer to provide a result, especially if the column is not indexed. Now complicate that with two somewhat simultaneous transactions. Before the first has done the insert, the second has obtained the same max(orderno) as the first - but you are seeing those symptoms now.
I imagine that your transaction is already in production and you do not want to change the orderno to an identity column. Hopefully, your row-build and insert logic is not to convoluted so as to take a lot of time. Maybe, change the processing order: build the item and then obtain max(orderno) just before the insert.
but, your methodology to determine orderno is the problem. Use another table to contain control info - such as last_used_orderno, open a cursor, select for update, add 1 then update the row containing your control info. then other tasks are forced to wait on the completion of the 'current transaction' - the comit will release.
You said LOCK- what kind of lock? I hope you are not issuing an explicit lock....
as a stopgap measure until you can decide how you are going to fix this design problem, you can just loop on your insert (when you get the dup error), adding 1 each time to the originially retrieved max(orderno) - don't do another max.
do your order numbers need to be contiguous? If not, use a combination of date and eibtaskid - then your problem is solved and you save the MAX stuff......and hopefully it is not WITH UR................... _________________ Dick Brenholtz
American living in Varel, Germany
Last edited by dbzTHEdinosauer on Wed May 16, 2007 3:11 pm; edited 1 time in total |
|
Back to top |
|
 |
vijay Beginner
Joined: 09 May 2003 Posts: 131 Topics: 64
|
Posted: Wed May 16, 2007 2:48 pm Post subject: |
|
|
Thanks for your input.I've one more question .If say tran1 issues a read with update on a vsam record,and before tran1 is complete another transaction tran2 does a read update on the same record,does tran2 wait till tran1 releases lock on the vsam or you'll get a file status <> 0 for tran2 when it is doing read for update on the same record? |
|
Back to top |
|
 |
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Wed May 16, 2007 3:25 pm Post subject: |
|
|
vijay,
you are more than welcome.
your last question needs to be in the 'Data Management' Help Board (8th in the list, after the 'TSO and ISPF' board, for multiple reasons:- this is a Database Board and many of the VSAM experts won't look at this board, thus depriving you of the resources to be found there
- no one will be able to find the topic with search - your have a good question of which I am sure many would have an interest.
- I have not had to mess with VSAM for a few years and I know that there are a lot of things that have to be considered to give you a good answer - I know that I would miss something.
You provided an excellent Title to this thread which will help people when they search. Please take the time to do so with your new thread. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
 |
vijay Beginner
Joined: 09 May 2003 Posts: 131 Topics: 64
|
Posted: Fri May 25, 2007 11:05 am Post subject: |
|
|
Thanks Guys.
I found another problem that was causing contention.
Here are the details
1.Data enetered on web initiates a transaction PROG01.
PROG01 does a cics start
EXEC CICS
START TRANSID ('PB01')
FROM (PB01-AREA)
PROTECT
END-EXEC.
The CICS start is done more than once (twice in the same program)to do different kind of updates for a particular customer no.
I guess we're getting deadlocks bcos in trans PB01 bcos
1.PROG01 starts PB01 more than once say twice for the same customer(different data for the same customer) at the same time
2.Because PB01 is running at the same time and working on the same customer ,
somewhere it's causing a deadlock.
3.I'm thinking of putting a INTERVAL on the start command when it is invoked second time by PROG01.
Do u think this will help?
Vijay |
|
Back to top |
|
 |
|
|