Re: Oracle Lock

> Hello, my name is Roman Kirillov. I have some
> Oracle issue and I need help. Our company used Microsoft SQL before, but
> about 8
> months ago we switched to Oracle. Most of our code was transferred
> without any issues except this one. We used to do ROWLOCK in our SQL
> commands when writing ATL applications for accessing dbases, but with
> Oracle
> it does not seem to be working. Do you know of any good way to lock the
> row in Oracle after or during selection that no one else can modify it,
> then update it. I really need to have it in some of my C++ applications
> (not letting anyone touch my row until I do update). There is has to be
> a way to make it part of SQL command.
> I looked in documentation for Oracle, but did not find anything on row
> lock. Seems very strange because this issue is very important for any
> programmer.
>
> I give an example how we used to do this in Microsoft SQL, could you
> please help on this issue with Oracle.
>
> Example of Microsoft SQL row lock and update:
> SELECT mins.min_id, mins.min_num FROM mins WITH (ROWLOCK) WHERE
> (mins.status = 'F' OR mins.status = 'U') AND mins.lca_id = 'somenumber'
>
> UPDATE mins WITH (ROWLOCK) SET status = 'A' WHERE min_id = 'someminid
> from previous select'

My suggestion would be to throw the code away. SQL Server's version of row locking is somewhere between anemic ... pathetic ... and nonexistent. Oracle's on the other hand is very robust and the database engine handles almost every situation seamlessly without program intervention.

If you find a problem, and only if you find a problem, look at the built-in package DBMS_LOCK.

Daniel A. Morgan Received on Thu Mar 08 2001 - 07:34:47 CET