| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Opinions needed: Locking strategy
On 10 Feb 1997 11:50:00 GMT, "Stephan Voigt" <svoigt_at_voigt-gmbh.de> wrote:
>Hi,
>
>I´am using Oracle workgroup server 7.2.2 on netware and Delphi 2.01 on NT
>4.0 as frontend.
>
>I develop several applications in a multiuser environment. Like all
>sql-servers oracle performs standard locking on rows which are updated
>during transactions. These rows are unlocked upon commit or rollback. If
>two users try to update the same record, oracle waits until the record can
>be locked. But in my point of view, this is not very fine for a
>well-designed user-interface, when a user has to wait several minutes or
>hours for a lock.
>
>In my application, i want to check for a lock by another user before I try
>to update any record in order to prevent that the user must wait too long.
>I have already found something about the DBMS_LOCK package of oracle.
>Has anyone experiences with it ? If yes, please tell me.
>Are thery other ways for a good locking strategy ?
>
>Thanx
>
>Stephan Voigt
the following logic not only prevents the waiting on the update but prevents lost updates as well. it is exactly the logic the Oracle forms performs automatically for you.
1.) select the data out of the database for display to the end user, use a simple select. Include the rowid for the data. For example:
select rowid, ename, deptno, loc from emp
2.) when the user wants to change the values of the data issue:
select * from emp
where rowid = :theRowidYouFetched
and ename = :theEnameYouFetched
and deptno = :theDeptnoYouFetched
and loc = :theLocYouFetched
3.) if the above returns 1 row and no errors, you 'own' the row. It is locked for you.
if the above returns an error, the row is already locked by someone else. You can wait and retry (forms for example tries 3 times before giving up).
if the above returns successfully but returns 0 records then you know that someone else has updated/deleted the record you were interested in.
4.) update the record after the user modifies the values.....
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |