MVSFORUMS.com

[maximus2007]

Assume that one user is updating a row in a table through an application.
Now if another user is tries to update that same row(at the same time) he should not be able to update the row. I am planning to throw a message to the second user saying "This row is being updated by some other user".

Assume that there are two users u1 & u2.

And there is an table Emp_tab. In that emp_id is the primary key.

Emp_tab
---------------------------------------------------------------------
Emp_id Emp_name Emp_dob Emp_stat_ind
---------------------------------------------------------------------
1000 aaa 12/12/1983 A
2000 bbb 12/12/1983 B
3000 ccc 12/12/1983 A
4000 ddd 12/12/1983 A

Now assume that both the users U1 & U2 have loaded the employee 2000 in their frontend application. And they are trying to change the EMP_STAT_IND to C and D respectively.

Now in my program i have the logic like the old row will be expired and the new row will be inserted. Now in this case both(C & D) EMP_STAT_IND are added to this employee.

So i want to avoid this by locking the row.


Now the first user should lock the row before updating.
So please let me how to lock that particular row.

Thanks in advance,
balaji[img][/img]

[dbzTHEdinosauer]

why don't you provide enough conditions in your UPDATE WHERE clause to insure that no other task has modified this row since you SELECTed it and are now, based on the user's changes, attempting to UPDATE?

If the WHERE clause fails, (-100 on the UPDATE), re-SELECT the row, re-display the row to the user and then attempt, again, to UPDATE the row based on 'new' WHERE condition values.

If your application database is so small that you will often have have contention among users, then possibly add a new column - IN_UPDATE_PROCESS - that your first SELECT would modify, to effectively lock the row.

others on the board will offer other suggestions.
_________________
Dick Brenholtz
American living in Varel, Germany

[dbzTHEdinosauer]

I apologize. My last post was so stupid as to require censure.

I assume these are an online applications. As a result, any lock issued will be release when you RETURN to the user.

Suggest implementing a LAST_UPDATE_TIMESTAMP.

During the UPDATE of a row, you would SET this column to CURRENT_TIMESTAMP.

When you select a row for the user, save the LAST_UPDATE_TIMESTAMP value in DFHComm

During the UPDATE of a row, your WHERE clause would include
WHERE LAST_UPDATE_TIMESTAMP = :SAVED-LAST-UPDATE-TS. IF you receive a -100 on the UPDATE, you know that another user has UPDATed this row, since your select.
_________________
Dick Brenholtz
American living in Varel, Germany

[kolusu]

maximus2007,

Check this link which explains all about LOCKING

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPH11/4.2.4?DT=20010710165542

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu