Re: Query to find first missing integer in a field
Date: Wed, 8 Apr 2009 18:45:27 -0700 (PDT)
Message-ID: <41373f57-b8d7-4364-8250-e5fc3343c075_at_37g2000yqp.googlegroups.com>
There is no secret answer that people are withholding from you. What you have is what there is.
The real issue isn't how you get the "next unused number"; it's the fact that you'll have to lock the table while you do it. That means that only one session can insert at a time, and if a session is inserting, all other updates and deletes will have to wait for that to finish. If a session holding that table lock hangs, or dies unexpectedly, or the network slows for some reason, your application will stop dead. These considerations may or may not be relevant to you.
However, if you *must* go down this road, you have to come up with a way of doing this that's fast, and looking up numbers on the fly is never going to be fast enough. I suggest that you work out all the unused numbers *once* and load them all into a separate table. Delete them as you use them. You'll have to lock that table to get the "next unused number", but that will probably make the duration of the lock shorter, and won't hold up updates or deletes on the main table. It won't be good, but it will be less bad.
Review how you detect and deal with blocking locks, if you haven't already.
- Phil