|
View previous topic :: View next topic
|
| Author |
Message |
suresh.easy
New User
Joined: 14 May 2008
Posts: 1
Location: bangalore
|
|
|
|
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:
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
