Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Select for update

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
suresh.easy

New User


Joined: 14 May 2008
Posts: 1
Location: bangalore

PostPosted: Thu May 15, 2008 4:04 pm    Post subject: Select for update
Reply with quote

We have a requirement where we do the following:

1) Select current application number value from table.
2) Calculate new application number value. (add plus 1)
3) Store new application number value back to database with UPDATE
statement.

NB: Appl. number is char field.

When several users try to get an application number at the same time, for some users during step 3 (update), it gives "Data

has been changed by another user. Start again" as the timestamp is changed. This is bit irritating to the users and

confusing. To avoid, one solution is to use SELECT FOR UPDATE putting a rowlevel lock but read in one of forums that this

approach also causes problems:

http://bytes.com/forum/thread482255.html

Once a row is locked by a user, for all others who try to access that row gets a deadlock (what are the possible sqlcodes?)

One approach we thought was to allow deadlock sqlcodes as OK codes and try for 5 times to see if the lock is released before

giving up. Please suggest any other solution that you can think of.

Any way to use both select and update in the same sql.

Regards,
Suresh
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 15, 2008 5:01 pm    Post subject:
Reply with quote

deadlock sqlcode is -911.

a few questions ?

1. will there be only one ie the latest appl number in the table or you want the latest to be modified and updated ?

2. "Data has been changed by another user. Start again" as the timestamp is changed - how is this error thrown ?

Select for Update is a intent-to-change type of transaction and during this the locks acquired can be one of Share, Update or Exclusive.

So here a better option is to check a few times for getting an exclusive lock on the table and then update accordingly ...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu May 15, 2008 5:49 pm    Post subject:
Reply with quote

Quote:

2. "Data has been changed by another user. Start again" as the timestamp is changed - how is this error thrown


seems as if an application program, attempting to go thru the 3 steps, timed out on step 1 or step 3 and issued the message - cics will not do this.

good logic would to put a controlled loop for x number of times to attempt to achieve an acceptable new char number.

Quote:

NB: Appl. number is char field.


why oh why do people insist on not using the correct data type when defining a column????

even so, why is it taking so long for an application program to:
do a select for update,
go thru the gyrations of adding 1 to a char field,
and issuing an update.

this process should not take so long. only the last of many, many similar tasks should be timed-out.

and a timed-out task which knows that it is often in contention should suspend for a few seconds, then retry.

keep in mind there is a retry limit defined to db2. often set at 10, and programmatically trying 10 times means 100 attempts.

I also believe this is poor programming to react to contention by telling the user to try again.

1. insure that the processing done between the select for update and the update is as little as possible.

2. programmatically loop thru and retry 10 or so times before telling the user he has to try again.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu May 15, 2008 9:21 pm    Post subject:
Reply with quote

Hello,

Quote:
Once a row is locked by a user, for all others who try to access that row gets a deadlock
I believe this is not accurate. Once a row is locked, other "users" are forced to wait. . . The -911 should not be raised in this situation.

A deadlock occurs when there is an overlap of lock attempts by two different processes. Each process attempts to lock the same 2 rows in not the same sequence of locking. FWIW.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us