Re: Commit and ANSI SQL

smj_at_jbecpor.demon.co.uk (Martin Jarvis) writes:

>I've just had a discussion with some colleagues about the use of Commit in
>Oracle. In particular that a commit will save _all_ outstanding changes. My
>boss seems to think that other databases allow concurrent transactions,
>thereby allowing individual changes to be saved separately.
 

>What does the ANSI standard for SQL say about this ?

You are mistaken about the behavior of a COMMIT in ORACLE versions 5 through 7 (the only ones I've used). Specifically, a COMMIT will cause all posted changes in the current cursor (when using a C program) or the current session (using SQL*Plus) to 'happen', i.e. be visible to other users, other cursors, etc. But a COMMIT by me will not cause un-committed changes in *your* session or cursor to 'happen'.

Other front ends (like SQL*Forms, which uses cursors) should clearly behave similarly. I've demo'd this with multiple SQL*Forms sessions hitting the same table and modifying alternately the same and different rows to demonstrate row-level locking and how Oracle automagically implements concurrency control.

What on Earth makes you think anything to the contrary? Or am I misunderstanding your question?

> Martin Jarvis | Internet - smj_at_jbecpor.demon.co.uk
> John Brown E & C Ltd | X.400 - Martin Jarvis /JBEC-Portsmouth, IT
> Portsmouth, England |

Cheers.

-- 
Thomas Cox      DoD #1776   '91 CB 750 Nighthawk   tcox_at_qiclab.scn.rain.com
    The Platinum Rule:  "Do Unto Others As They Want To Be Done Unto"