how to alloc records to avoid an update at the same time with another user

i'm using select sql (DB2 on IBM i) to read a record from database and show it into a form where an user can modify and update it!

now i need that if an user is viewing this record, at the same time another user can only view but not update the same racord!

how can i do it? ther is a way to select this record allocating (or blocking) the record to avoid an update at the same time? (also if there is same php instructi开发者_如何学Pythonon to do it)

(for example i working with RPG language program where i use CHAIN instruction that alloc record to prevent this problem)

thanks!

In my experience you need to avoid going down this route if you possibly can.

Yes it is possible to lock the record, but how long do you intend to keep the lock? What happens if your locking user goes for a long lunch before hitting 'save'?

Faced with the problem you have, I would look at doing a read check of the record before updating. If you find it has been changed by another user you can flag this up or work in some logic to change only modified fields.

Locking the row can be useful if you control all access to the table, for instance a green screen application. If you don't control all access, for instance someone else has a web application that reads rows, or an ODBC application reads rows, they might be frustrated at the inability to get at all the rows. Definitely consider the larger role the table plays in all applications accessing it.

That said, the DB2 SQL way to lock the row is FETCH ... FOR UPDATE.

What about avoiding locked records reading?

Select * from yourtable
where field='comparator'
SKIP LOCKED DATA

Also you can retrieve a specific number of rows

Select * from yourtable
where field='comparator'
SKIP LOCKED DATA
FETCH FIRST 1 ROWS ONLY

Does it fit you?

Yes you can lock the table while you are updating the data. I don't know how can you do it on DB2, but propably you can lock the tables while updating your data, and unlock them after finish.

May this link can help you:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000972.htm