Oracle is a commercial solution that has steep pricing options, with additional payments required for extra features. The transaction can continue (like re-try, or do an alternative change): sqlplus # Oracle Database 19.4 column t format a35 exec for i in (select table_name from user_tables where table_name='DEMO') loop execute immediate 'drop table "'||i.Both methodologies are very popular today, but PostgreSQL has gained market share recently and is trending upwards because (some have claimed) it has features more suited to today’s dynamic development requirements. Review five crucial database parameters and decide for yourself. Still in Oracle, when a statement fails in a user call, the modifications done by this user call are rolled back, but not the previous modifications. What I show here is about a user call statement which can actually be a SQL statement or a PL/SQL block or a procedure call ). (Note that I’ve previously written “statement-level rollback” but this is misleading because a statement can be executed in a recursive call, as in a PL/SQL block. The PADDR is only the address or the top-level one. This shows that there can be multiple transactions for the same session. sqlplus # Oracle Database 19.4 set serveroutput on create table DEMO2 as select * from DEMO select saddr,taddr from v$session where sid=sys_context('userenv','sid') select count(*) from DEMO select saddr,taddr from v$session where sid=sys_context('userenv','sid') update DEMO set t=current_timestamp select saddr,taddr from v$session where sid=sys_context('userenv','sid') select addr,ses_addr from v$transaction declare pragma autonomous_transaction begin update DEMO2 set t=current_timestamp for i in ( select addr,ses_addr from v$transaction) loop dbms_output.put_line( 'Transaction: ADDR: '||i.addr||' SES_ADDR: '||i.ses_addr ) end loop rollback end / rollback quit ![]() This is very convenient in some limited cases, like logging the error in the database (and commit this insert) before the rollback of the transaction. Not available in PostgreSQL but possible in Oracle: we can have nested transactions. This illustrates when a transaction starts (visible with a TADDR in V$SESSION and a row in V$TRANSACTION): the first INSERT/DELETE/UPDATE/MERGE/SELECT FOR UPDATE or a SET TRANSACTION Autonomous Transaction This example looks at the transaction address in V$SESSION and V$TRANSACTION sqlplus # Oracle Database 19.4 select saddr,taddr from v$session where sid=sys_context('userenv','sid') select count(*) from DEMO select saddr,taddr from v$session where sid=sys_context('userenv','sid') update DEMO set t=current_timestamp select saddr,taddr from v$session where sid=sys_context('userenv','sid') rollback select saddr,taddr from v$session where sid=sys_context('userenv','sid') set transaction name 'my_transaction' select saddr,taddr from v$session where sid=sys_context('userenv','sid') quit The SET TRANSACTION starts it, in order to define the isolation level, or simply to put a name to the transaction. Set Transaction nameĭon’t think that there are no “begin transaction” in Oracle. ![]() Note that START TRANSACTION is the ANSI SQL syntax, but PostgreSQL accepts also BEGIN, BEGIN TRANSACTION and BEGIN WORK. Now, who is the winner in transaction management? There’s even more: you can send a multi-statement command to the backend and it will be processed as an implicit transaction. ![]() And even the last INSERT was implicitly rolled-back on exit. But now that I explicitly started a transaction, everything was transactional, even the DDL: the DROP TABLE, the CREATE TABLE, the INSERT were explicitly rolled-back. See? I have the 3 rows from the first run which were all auto-committed. psql -U postgres demo # PostgreSQL 11.4 start transaction drop table if exists DEMO create table DEMO as select 1 n,current_timestamp t select * from DEMO insert into DEMO values (2,current_timestamp) select * from DEMO rollback select * from DEMO start transaction insert into DEMO values (3,current_timestamp) \q psql -U postgres demo # PostgreSQL 11.4 select * from DEMO \q I can explicitly start a transaction and then I’ll have to explicitly commit it. Start transactionīack to PostgreSQL, the default is auto-commit but I have the choice. That’s the Oracle Database, nothing to do with the client: you cannot be transactional with DDL. ![]() It was inserted with DDL (CREATE TABLE) and DDL are always auto-committed. DDL auto-commitĪnd that’s not all… The first row (n=1) was never explicitly committed. (also recommendedvfor scripts having a WHENEVER SQLERROR EXIT that mentions ROLLBACK because COMMIT is the default). It is highly recommended to set exit-commit off to avoid any unexpected commit. If I quit without saying ‘commit’ I want a rollback. Not because of auto-commit but because of exit-commit: SQL> show exitcommit exitcommit ON Are sqlplus defaults so smart? I didn’t commit the last insert but it was committed by default.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |