MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Concurrent Update problem

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
vijay
Beginner


Joined: 09 May 2003
Posts: 131
Topics: 64

PostPosted: Wed May 16, 2007 7:44 am    Post subject: Concurrent Update problem Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Wed May 16, 2007 8:02 am    Post subject: Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Wed May 16, 2007 8:26 am    Post subject: Reply with quote

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
View user's profile Send private message
vijay
Beginner


Joined: 09 May 2003
Posts: 131
Topics: 64

PostPosted: Wed May 16, 2007 2:48 pm    Post subject: Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Wed May 16, 2007 3:25 pm    Post subject: Reply with quote

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
View user's profile Send private message
vijay
Beginner


Joined: 09 May 2003
Posts: 131
Topics: 64

PostPosted: Fri May 25, 2007 11:05 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group