Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Red face Unanswered: Creating a Sub-Transaction in PL/SQL?

    Hi,
    I'm facing a problem with LOCK TABLE IN EXCLUSIVE MODE. The following excerpt explains my problem:

    procedure myproc_2
    begin
    .
    .
    .
    commit;
    end myproc_2;

    procedure myproc_1
    begin

    LOCK TABLE DUAL IN EXCLUSIVE MODE;

    myproc_2;

    commit; -- Should release the lock on DUAL !
    end myproc_1;

    It seems the since myproc_2 makes a COMMIT it releases the lock on DUAL and allows other transactions that were locked on DUAL to continue!!!. Is it possible to create a sub-transaction which starts after the LOCK statement to permit commit inside myproc_2 and keep the lock on DUAL until the commit of myproc_1 is executed?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: Creating a Sub-Transaction in PL/SQL?

    Hi.

    The feature you are looking for is called: AUTONOMOUS TRANSACTION, and it's availablity depends on your dbms release.

    Take a look at oracle reference.
    see you soon.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  3. #3
    Join Date
    Aug 2002
    Posts
    6

    Re: Creating a Sub-Transaction in PL/SQL?

    Thanks for the reply. I'm using Oracle 7.x.x which doesn't support autonomous transaction...

    See u

  4. #4
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: Creating a Sub-Transaction in PL/SQL?

    Hi.
    "there was a time ... "

    don't be too acid with your old fashioned, pre web, db release.

    Oracle7 was a big step forward in database technology,
    for us survived to the stone age.

    Your actual requirements push for a migration to a newer release.

    good like.
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

  5. #5
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: Creating a Sub-Transaction in PL/SQL?

    you could potentially (really depends on what you are doing in that procedure) submit myproc_2 into the job-queue and thus run myproc_2 asynchronously instead of doing the synchronous invocation.

    dbms_job.submit (Jobnrout,'begin myproc_2;commit;end;');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •