Home » SQL & PL/SQL » SQL & PL/SQL » Avoid breakup in the sequence (9i)
Avoid breakup in the sequence [message #438193] Thu, 07 January 2010 08:07 Go to next message
santosh4483
Messages: 15
Registered: November 2009
Junior Member
How to avoid the breakup in the sequence?

For example: i created a table with three columns a,b,c
i created sequence s1. i used this sequence for inserting values for one of the column in the table.Parallely other user also using same sequence for some other table.So,obviously i will get the breakup for my table while inserting.

To avoid this scenario please give a solution for this...

Thanks,
Santosh
Re: Avoid breakup in the sequence [message #438195 is a reply to message #438193] Thu, 07 January 2010 08:21 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
If by "breakup", you mean gaps in the sequence, you can't really avoid them. You could minimize the risk of getting gaps but each time you select a sequence.nextval, that value is used and cannot be used again. Let's say you insert using nextval but you rollback the transaction. That sequence value is not available anymore. Sequences guarantee a UNIQUE value ( as long as the sequence doesn't cycle, that is) each time you select from it, regardless of how many users are using that sequence.

So, if you want to avoid gaps in your sequence: you can't. And what is the problem with gaps?

MHE
Re: Avoid breakup in the sequence [message #438198 is a reply to message #438193] Thu, 07 January 2010 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could use a sequence per table rather than one sequence for multiple tables as you are doing at the moment, but as Maaher says you can't completely avoid gaps with sequences.
Re: Avoid breakup in the sequence [message #438199 is a reply to message #438193] Thu, 07 January 2010 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have some problem when numbers are not in sequence then you have a bigger problem: a problem of design.

The only way to avoid gaps is to lock the sequence (whatever it is) during your work and serializable all transactions that use it.

Regards
Michel
Re: Avoid breakup in the sequence [message #438205 is a reply to message #438199] Thu, 07 January 2010 08:47 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course, if like with santosh here a sequence is used for two tables then you get a lot more gaps in each table than if you only use one sequence for one table.
Re: Avoid breakup in the sequence [message #438208 is a reply to message #438199] Thu, 07 January 2010 08:52 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
When faced with a requirement that there should be no gaps in a sequence, it usually means that the column is being used inappropriately. A sequence is designed to generate unique values; what are unique values used for? Primary key columns, where gaps are not a problem. But if the column is being used for business purposes, such as VAT invoice numbers which (in Europe, anyway) should not have gaps, then a sequence is not the structure that should be used.
With appropriate business analysis and system design, the problem should disappear.

[Updated on: Thu, 07 January 2010 08:55]

Report message to a moderator

Re: Avoid breakup in the sequence [message #438212 is a reply to message #438199] Thu, 07 January 2010 09:13 Go to previous messageGo to next message
santosh4483
Messages: 15
Registered: November 2009
Junior Member
Michael,

Please can you give one example how to lock the sequence?

Thanks,
Santosh
Re: Avoid breakup in the sequence [message #438213 is a reply to message #438212] Thu, 07 January 2010 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441

Re: Avoid breakup in the sequence [message #438214 is a reply to message #438212] Thu, 07 January 2010 09:18 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Locking the sequence will not solve your problem when you use the sequence for more than one table.

It will also only solve one cause for gaps, there are other causes for gaps as well.

So it is definitely not the direction you should be going.
Re: Avoid breakup in the sequence [message #438217 is a reply to message #438212] Thu, 07 January 2010 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
santosh4483 wrote on Thu, 07 January 2010 16:13
Michael,

Please can you give one example how to lock the sequence?

Thanks,
Santosh

What I mean by locking the sequence (whatever the sequence generator is) is that you use a semaphore, a lock point, and try to get it when you want a number and release it when you are over with this number.
This lock can be a table lock if you put the sequence number in a table. It can be a user defined lock (see DBMS_LOCK package). It can be a signal (see DBMS_ALERT package); And so on.

But whatever it is, it means you enter in a single transaction world (for al of them needing the sequence number).

Regards
Michel

[Updated on: Thu, 07 January 2010 09:55]

Report message to a moderator

Re: Avoid breakup in the sequence [message #438249 is a reply to message #438193] Thu, 07 January 2010 12:15 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here are some basic facts:

1) an Oracle sequence cannot be locked.

2) an oracle sequence will NEVER generate an unbroken sequence.

These are not "maybe true" they are fact.

Thus, if you absolutely need a sequence that generates numbers without gaps, you cannot use an Oracle sequence. This is just how they were made, and for good reason too. One need only consider the consequences of gapless sequence generation in a concurrent environment to understand why.

I could wax long winded about these two facts but it won't change anything.

Before the days of oracle sequences, we did it the old way. Here is an example:

--
-- we create a table to keep our sequence
--
create table number_sequence (aninteger integer);
insert into number_sequence values (0);
commit;

--
-- create a function to get the next sequence value
--
create or replace function f_get_next_number return integer is
   return_v integer;
begin
   update number_sequence set aninteger = aninteger + 1;
   select aninteger into return_v from number_sequence;
   return (return_v);
end;
/
show errors

--
-- use the function in your code
--
begin
   for r1 in (select * from user_tables where rownum < 10) loop
      dbms_output.put_line(f_get_next_number);
   end loop;
   commit;
end;
/


Notice the following:

1) the update/select sequence is how you get the next number.

2) because you are updating a table in order to generate a sequence number, a rollback in your transaction will result in a rollback of the sequence table as well. This is how you manage to get a gapless number.

3) notice the location of the commit. This ends your transaction, starts a new one, and with respect to the sequence, finalizes the allocated numbers as allocated. Thus you must now really plan where you put your commits.

4) you cannot commit until your are done with your transaction. That means your system has now basically become a single user system because the next guy who wants a sequence number from your function must wait till you do a commit. You are all sharing the same sequence which translates to locking (because of the update) the same row in the same table.

That is why Oracle sequences don't work this way. Oracle worries a lot about concurrency and so cannot adopt such a limiting solution to sequences. They adopted a gap possible implementation in order to achieve high concurrency. For those of us who have been around for fifteen years or more you may recall the golden days where sequence generation was a hot topic and different vendors were trying to figure out how to do it. The basic problem was always that having to serialize on a single row inherently limited the thoughput of a system. It was a big problem and never really solved until oracle sequences arrived. And as you can see, for those who want a gapless number, it still has not been solved.

Indeed, even with the above solution, there is no way for you to guarantee that you won't have gaps in a concurrent environment because though you may be able to pull a series of number gapless in your transaction, once you commit someone else can come in a pull a series of number off the sequence. Thus if you go back to it expecting to pick up where you left off, you may not be able to. This is what we mean by defining your transactions carefully.

As you can see, a gapless sequence is really a pipe dream for most people. If you are asked to do it, make sure you get a real good explanation of why it is necessary and try to explain why it is a bad idea to the people who think they need it.

Good luck, Kevin
Re: Avoid breakup in the sequence [message #438257 is a reply to message #438249] Thu, 07 January 2010 12:31 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Like the way you try to make people understand.It really helps!!
Thanks you very much!!

Love to read each post/article written by you.


Regards,
Ved
Re: Avoid breakup in the sequence [message #438265 is a reply to message #438193] Thu, 07 January 2010 13:29 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks.

There are some rare cases where gapless sequences are required but they always involve some form of auditing associated with them. The classic example is check generation. Checks generally have pre-printed numbers on them. When computers started doing check processing there was a lot of effort put into how to generate a check in such a way that it was possible to prevent the "disappearance" of checks from a batch and used illegally later. There are some auditing practices that validate based on the idea of a gapless sequence in preprinted checks.

But outside of this, in my experience, everyone who has claimed to need a gapless sequence, really didn't. The best way to figure it out is to ask what the audit processes are that will take advantage of the gapless-ness of the sequence. When the customer cannot demonstrate the value of the gapless nature of the sequence, they don't need gapless.

Good luck, Kevin
Re: Avoid breakup in the sequence [message #438339 is a reply to message #438249] Fri, 08 January 2010 03:21 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Great example, Kevin. One remark though:
Kevin Meade wrote on Thu, 07 January 2010 19:15
Here are some basic facts:

1) an Oracle sequence cannot be locked.

2) an oracle sequence will NEVER generate an unbroken sequence.
I have a little bit of a problem with the "will NEVER generate" in your second point. Especially in combination with the bold statements that this is not "maybe true" but fact. Are you 100% sure that that is a fact? It is not that something is unlikely that this will never happen. In concretum: An Oracle sequence can be generating consecutive numbers without ever leaving gaps. Theoretically. That is fact, I think. Allow me to demonstrate:
SQL> create table mhe_seqtab(seq_col number)
  2  /

Table created.

SQL>
SQL> create sequence mhe_seq start with 1 increment by 1
  2  /

Sequence created.

SQL>
SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> insert into mhe_seqtab
  2  values ( mhe_seq.nextval )
  3  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL>
SQL> select seq_col
  2  from   mhe_seqtab
  3  order  by 1
  4  /

   SEQ_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8

8 rows selected.

SQL> drop sequence mhe_seq
  2  /

Sequence dropped.

SQL> drop table mhe_seqtab purge
  2  /

Table dropped.

SQL>

Perhaps you meant
2) an oracle sequence will NEVER guarantee an unbroken sequence.
And that's an entirely different story.

Just nitpicking, I do follow your general idea and maybe I'm way off with my reply. Embarassed

MHE

[edit]And just for fun: here's an old reply of my hand concerning sequences.

[Updated on: Fri, 08 January 2010 03:31]

Report message to a moderator

Re: Avoid breakup in the sequence [message #438343 is a reply to message #438339] Fri, 08 January 2010 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A case of break in the sequence:
SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;
   NEXTVAL
----------
         1

1 row selected.

SQL> /
   NEXTVAL
----------
         2

1 row selected.

SQL> /
   NEXTVAL
----------
         3

1 row selected.

SQL> @sys
Connected.
SYS> shutdown abort
ORACLE instance shut down.
SYS> startup
ORACLE instance started.
Total System Global Area  314572800 bytes
Fixed Size                  1296428 bytes
Variable Size             130025428 bytes
Database Buffers          171966464 bytes
Redo Buffers               11284480 bytes
Database mounted.
Database opened.

SYS> @c
Connected.
SQL> select s.nextval from dual;
   NEXTVAL
----------
        21

1 row selected.

SQL> /
   NEXTVAL
----------
        22

1 row selected.

Regards
Michel
Re: Avoid breakup in the sequence [message #438346 is a reply to message #438343] Fri, 08 January 2010 04:02 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Thanks for confirming my reply: a sequence CAN break, but it can also produce consecutive numbers. Kevin was talking about facts. One fact Kevin stated was that sequences would NEVER produce consecutive numbers (Meaning, in my world, that no one can ever produce a counterexample). I merely suggested a change in the sentence so that it would become a true fact. That's all. Perhaps I misread his question, but this is how I interpreted it and I stand my ground.

MHE

[Updated on: Fri, 08 January 2010 04:09]

Report message to a moderator

Re: Avoid breakup in the sequence [message #438354 is a reply to message #438193] Fri, 08 January 2010 05:23 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I read Kevin's statement to mean that if you use a sequence, sooner or later you will get gaps. Sure if you run a small test case you almost certainly won't get gaps, but if you're using it in a real production system, considering the number of ways gaps can happen, it'll only be a matter of time before you get some.
It's like when you load a table with a small amount of data. If you query it without an order by you'll probably get the records in the order they were inserted. Doesn't mean that is behaviour you can rely on when writing code.
Re: Avoid breakup in the sequence [message #438361 is a reply to message #438354] Fri, 08 January 2010 05:47 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I think we're getting too far off here, but I'll try to explain my initial reply and how I read it.

Kevin stated that sequences will NEVER (!) be gap-free.
Kevin told that this was not 'maybe true' but FACT (!).

And although my example is perhaps not representable for the "real world", it does demonstrate that the FACT that sequences will NEVER be gap free, might be wrong. Facts are verifiable and objective.

That's all, not worth the discussion, actually. Nitpicking, like I said.

Bottom line:
It is a bad idea to rely on a sequence for generating 100% consecutive numbers (I never disagreed, you know) and Kevin made that perfectly clear. Thanks again Kevin, and sorry for hijacking this thread. Razz

MHE

[Updated on: Fri, 08 January 2010 05:48]

Report message to a moderator

Re: Avoid breakup in the sequence [message #438374 is a reply to message #438193] Fri, 08 January 2010 07:31 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks everyone.

I am OK to accept a rewording of my statements if it provides a clearer explanation of what we all mean.

CookieMonster's interpretation that there will eventually be gaps is what I meant and what I described in the narrative.

Maaher's restatement may be a clearer one liner of same.

After all, my description was in effect only a restatement of what Maaher said in the very beginning anyway and what Cookiemonster confirmed right after him. Followed of course by Michel's clear statement that it is usually a design problem to go for gapless sequence generation to begin with. You guys said what needed to be said in the beginning, I simply put out th "BOLD" statement that let the OP know there was no ambiguity in it along with some basic history on the topic.

Either way we have all made it clear that you cannot relay on an Oracle sequence to provide a guaranteed gapless result and that generating a true gapless sequence is only workable for low concurrency systems. This is what the OP wanted to know and what others reading this thread will want to know.

Good luck all, I love it when people contribute. Kevin
Previous Topic: Dynamic SQL + Cursor + Bind variable isn't working
Next Topic: how to show sinlge line text data in seperated column wise
Goto Forum:
  


Current Time: Thu Apr 25 21:50:48 CDT 2024