Home » SQL & PL/SQL » SQL & PL/SQL » can i remove pragma autonomous_transaction in this scenario (oracle 10g)
can i remove pragma autonomous_transaction in this scenario [message #442262] Sat, 06 February 2010 04:18 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
we store startingslno, endingslno, currentslno in the table called codegenerator.what this procedure will do is after getting the latest serialno it back updates the current serial no to that table.for that we used pragma autonomous_transaction;if multiple users called this procedure they will get unique serial numbers.

but our problem is after calling this procedure we have written lot of transaction related code.basically whatever invoice number generated we will insert and update in multiple tables.

in this code if any exception occurs whatever serialno generated above will not be rolledback .all this code have been written in the same transaction block.

but our requirement is we dont want to lose the sequence number.

can i remove pragma autonomous_transaction and commit in the codegenerator procedure?

if i remove in the procedure i have one more concern?

if multiple users try to generate invoice at the same time what will happen to this CODEGENERATOR table?will dead lock occurs?
how to resolve this problem.my objective is don't want to lose the serail no.



{call getCustSerialnumber( 'INVOICE', 1000, 6, null )};

insert into CODEGENERATOR (SLNO, NAME,  STARTINGSLNO, ENDINGSLNO, CURRENTSLNO)
values (1, 'INVOICE', 1000, 999999, 1314);

CREATE OR REPLACE PROCEDURE getCustSerialnumber (
   p_codetype             VARCHAR2,
   p_startingslno         LONG,
   p_serialnolen          NUMBER,
   p_currsrno       OUT   VARCHAR2
)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   v_slnoforupdate   CODEGENERATOR.currentslno%TYPE;
BEGIN
   

   SELECT     TO_CHAR (LPAD (currentslno, p_serialnolen, 0)) currentslno,
              currentslno
         INTO p_currsrno,
              v_slnoforupdate
         FROM CODEGENERATOR CG
        WHERE CG.codeidname = p_codetype
          AND CG.startingslno = p_startingslno
          AND CG.currentslno <= CG.endingslno
     ORDER BY slno DESC
   FOR UPDATE;

   v_slnoforupdate := v_slnoforupdate + 1;

   UPDATE CODEGENERATOR CG
      SET CG.currentslno = v_slnoforupdate
    WHERE CG.codeidname = p_codetype
      AND CG.startingslno = p_startingslno;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('error ' || SQLERRM);
END;


[Updated on: Sat, 06 February 2010 04:27]

Report message to a moderator

Re: can i remove pragma autonomous_transaction in this scenario [message #442264 is a reply to message #442262] Sat, 06 February 2010 04:38 Go to previous messageGo to next message
John Watson
Messages: 6396
Registered: January 2010
Location: Global Village
Senior Member
Hi - I've had discussions (or perhaps "arguments" would be a better word) about this sort of thing many times. You have described the problem perfectly: to ensure that there are no gaps in the sequence of serial numbers, you must commit the increment of the serial number within the transaction; you cannot use a sequence, nor can you use an autonomous transaction. This means that your application cannot scale: concurrent users will serialize on record locks. Note that this is not a deadlock, it is a normal enqueue.
The mistake was made right at the start of the development life cycle, in the business analysis. Why can you not have gaps in the sequence? What is the bysiness need for this? In virtually all cases, there is no need. It is just that anally fixated accountants think that there must be no gaps. OK, there are generally accepted accountancy practices that mean that (for example) VAT invoices should be consecutive, but your auditors will accept that gaps occur.
Your only solution is to persuade your users that gaps don't matter. Then you can use a sequence to generate your unique values, and your performance problems will be solved.
Re: can i remove pragma autonomous_transaction in this scenario [message #442265 is a reply to message #442262] Sat, 06 February 2010 04:48 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Thanks for the detailed explaination.i have one question.

1.if i remove pragma autonomous_transaction and commit block what will happen?what kind of lock will happen?will it row level locking or table level locking?
Re: can i remove pragma autonomous_transaction in this scenario [message #442267 is a reply to message #442262] Sat, 06 February 2010 05:01 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
chaituu wrote on Sat, 06 February 2010 11:18
can i remove pragma autonomous_transaction and commit in the codegenerator procedure?

You can. What happened when you tried in your test environment?
chaituu wrote on Sat, 06 February 2010 11:18
if i remove in the procedure i have one more concern?

I do not understand this at all.
chaituu wrote on Sat, 06 February 2010 11:18
if multiple users try to generate invoice at the same time what will happen to this CODEGENERATOR table?will dead lock occurs?
how to resolve this problem.my objective is don't want to lose the serail no.

No deadlock, but simple lock. So the first user will continue and the others will wait until he ends his transaction. If you really want to assure gapless serial number generation, this is not problem, but demanded behaviour. Would you solve it in other way in real life? You might minimize the time between serial number generation and transaction end; e.g. by not using it in multiple tables as foreign key, but use surrogate key instead (of course this needs design change).

Just a few remarks to the code:
As WHERE condition differs between SELECT and UPDATE statements, more rows in CODEGENERATOR might be updated.
SELECT INTO might raise exception when none or more rows comply the conditions. This is successfully "masked" by EXCEPTION WHEN OTHERS section. I see only one reason for this: application is able to treat NULL serial number. But, why not to return NULL every time then?
Previous Topic: Exception : ORA-06502: PL/SQL: numeric or value error
Next Topic: Procedure Calls
Goto Forum:
  


Current Time: Sun Sep 25 21:24:21 CDT 2016

Total time taken to generate the page: 0.09922 seconds