-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Mon, Dec 03, 2001 at 10:31:11AM -0800, Rob Messer wrote:
> Perhaps more detail on your original example would be useful. I know from
> working with Oracle that if you feed it an insert with a primary key violation in
> the middle of a transaction, it will abort the transaction and rollback, just
> like PostgreSQL.
No, Oracle (and other ACID databases) will only rollback the *statement* on a
statement-level error, not the entire transaction. You can continue the
transaction from the last successful statement, or opt to roll the entire
transaction back (or roll back to a mid-transaction checkpoint, something I
believe is Oracle-specific). Either way, exception handling is an exercise left
to the application.
A common way of leveraging this in Oracle is for atomic check-and-insert
operations using a primary key. Visualize two transactions writing some
unique rows to a table. The first transaction to attempt the insert acquires a
SX lock on the (uncommitted) row, and a second transaction inserting a duplicate
row will block until the first commits (second gets a PK violation) or rolls
back (second gets to insert its row). In the former case the application can
handle the error as it desires, as the transaction is not destroyed, but only
the statement in error.
TX 1 TX 2
INSERT INTO foo VALUES (1,2,3);
COMMIT;
INSERT INTO foo VALUES (2,3,4);
(now has SX lock on uncommitted row
in foo)
INSERT INTO foo VALUES (2,3,4);
(Blocks on TX 2's SX lock)
INSERT INTO foo VALUES (1,2,3);
ORA-00001: unique constraint
violated
(still waiting on TX 2 to finish)
ROLLBACK;
(insert of 2,3,4 rolled back, lock
released)
(INSERT acquires SX lock and
completes normally)
INSERT INTO foo VALUES (3,4,5);
INSERT INTO foo VALUES (2,3,4);
(Blocks on TX 1's SX lock)
COMMIT;
(2,3,4 written to database)
ORA-00001: unique constraint
violated
INSERT INTO foo VALUES (4,5,6);
COMMIT;
(3,4,5 and 4,5,6 written to db)
The issue with PG seems to be an uncontrollable limitation of PG itself, in that
it forces a complete transaction rollback on any error. This is definitely the
Wrong Thing.
- --
Stephen Clouse <[EMAIL PROTECTED]>
Senior Programmer, IQ Coordinator Project Lead
The IQ Group, Inc. <http://www.theiqgroup.com/>
-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8
iQA/AwUBPAviQgOGqGs0PadnEQKFVACgqf918LRL4HBSJ0CgA+GLtbblXtoAn2HH
UHaSXJhAyXiT5gFDH/5RU3rF
=BKV1
-----END PGP SIGNATURE-----
