Re: locking & commit via OCI

In article <Wyrq7.27727$bG6.7088839_at_typhoon.we.rr.com>, "Massis says...
>
>
>Below is a pseudo sequence of events that I need to perform on Oracle via
>the OCI interface. I've written an application that does this with MS SQL
>Server using dynamic cursors through ODBC. It seems all the facilities are
>available to perform this in Oracle, however, I haven't been able to make
>this work through the combination of PL/SQL and the OCI interface.
>
>I'd really appreciate your suggestions/solutions.
>
> Thanks,
> Massis
>
>I need an OCI sample program written in C that demonstrates the following:
>
>
>TIME PROCESS 1 (P1) PROCESS 2 (P2)
>---- --------------------------------- ----------------------------------
>
>1 SELECT AMT FROM T1
> WHERE KEY=2 FOR UPDATE;
>
>2 SELECT AMT FROM T1
> WHERE KEY=1 FOR UPDATE;
> (Assume AMT=20)
> (RECORD IS NOW LOCKED BY P1)
>
>3 SELECT AMT FROM T1 WHERE KEY=1;
> (AMT = 20)
> (RECORD LOCKED BY P1)
>4 UPDATE T1 SET AMT=30
> WHERE KEY = 1;
> (NOTE: record must remain
> locked, and new value
> must be visable
> at read commited
> isolation level)
> (I've used dynamic cursors with
> "UPDATE ... CURRENT OF ..."
> to acheive this in SQL Server)
>

I don't know how you did that with sqlserver but ... in any ANSI SQL database -- the results of P1 are NOT allowed to be visible to any other process until P1 commits -- you have not committed (if you did commit, the lock would be released -- in Oracle, in SQLServer)

I can only guess that you are using a dirty read in P2 in SQLServer -- dirty reads are something we don't do (if you did not use a dirty read your subsequent select in P2 would BLOCK in SQLServer -- since readers aren't blocked by writers in Oracle -- we don't need, nor do we support, a dirty read.

I think you might need to go back and look at the SQLServer implementation - and see if you aren't using a dirty read.

You need to supply more details about transaction semantics you really used with sqlserver -- I believe you'll find you were doing a DIRTY read.

Read committed isolation, which is what you say you want, needs to have a COMMIT somewhere. The commit WILL release all locks.

>5 SELECT AMT FROM T1 WHERE KEY=1;
> (AMT=30, Note: picked up new
> value set by P1)
> (RECORD STILL LOCKED BY P1)
>
>6 SELECT * FROM T1
> WHERE KEY=2 FOR UPDATE
> (NOTE: This will block until P1
> releases the record)
>
>7 Release T1 WHERE KEY = 1
> (NOTE: T1 KEY = 2,
> record must remain
> locked)
>

You can only do this with TWO independent sessions, we can do it, you'll just be using two sessions to do so.

>8 Release T1 WHERE KEY = 2
> (NOTE: Process 2 will now
> unblock from step 6)
>
>

The sort of "locking" you are doing isn't the kind of locking relational databases do. This is not a "normal" thing, its not done the way you think you want to do it.

>
>
>
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp