Re: Oracle Lock

Two ways to accomplish this

define a cursor
cursor <curname>
is
select ...
for update of <any column>;
begin
for currec in <curname> loop

    update <table>
    where current of <curname>;
end loop;

order by is not allowed in the sql statement The other method is to issue an explicit lock table <tablename> in row share mode.

All locks will be released at the first commit.

Of course this is documented, and I believe Oracle was allowing row level locking lightyears before sqlserver.
Also there probably is a separate chapter on locking in the Oracle Concepts Manual.

Hth,

Sybrand Bakker, Oracle DBA

"Roman Kirillov" <rkirillov_at_equitel.com> wrote in message news:3AA68D9B.3091515_at_equitel.com...
> 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'
>
> I would really appreciate any help. Thank you very much.
> Sincerely,
> Roman.
> --
> ********************************************
> * Roman Kirillov
> * Software Engineer
> * equitel, inc.
> * 4021 Stirrup Creek Drive, Suite 400
> * Durham, NC 27703
> * Phone: (919) 419-5600
> * Direct:(919) 419-7758
> * Fax: (919) 419-5601
> * mailto:rkirillov_at_equitel.com
> * http://www.equitel.com
> ********************************************
Received on Wed Mar 07 2001 - 14:43:02 CST