Re: prevent 2 users updating same row

The solution might be "LOCK TABLE miketable IN EXCLUSIVE MODE;" This will make user B wait for user A to end selecting and updating before it can do any work on the table. Just be sure that you don't get your self into a deadlock situation!

If an EXCLUSIVE MODE lock is to hard try any of these (from sql reference):

• ROW SHARE allows concurrent access to the locked table, but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle.
• ROW EXCLUSIVE is the same as ROW SHARE, but also prohibits locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting.
• SHARE UPDATE--see ROW SHARE.
• SHARE allows concurrent queries but prohibits updates to the locked table.
• SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows.
• EXCLUSIVE allows queries on the locked table but prohibits any other activity on it

Hope this helps

Lehmann

<lfto_at_my-deja.com> wrote in message news:8rames$38r$1_at_nnrp1.deja.com...
> hi, i have the following situation where user A and B select the
> same row...then A updates and commits....then B update commits..
> problem is user B overlays user A updates....i need to prevent this.
>
> the select for update only locks other users from updating...not reading
> any ideas?
>
> here's my sql..
>
> user A: select * from miketable where user= 100;
> user B: select * from miketable where user=100;
> user A: Update miketable where user=100;
> commit;
> user B: update miketable where user=100;
> commit;
>
> thanks
> mike
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Oct 03 2000 - 04:40:13 CDT