Re: Stored Procedures - Getting a Unique Key

> > Well, after all my b*tching, I think I may have a solution to the > problem of unique key definition within stored procedures. > > My original complaint was - if stored procedures are oblivious to > the SQLCA record, two processes could very easily get into a race condition > where each of them is trying to use max(key-value) + 1 as a new key value. > > The suggestion had been made to make lavish use of locks, but I see that as > greatly impacting concurrency, particularly in light of the "twin" scenario. > > So, I'd like to submit the following two stored procedures for the Net's > review. Essentially, my first SP gets the max(key_nr) of the column. My > second procedure tries to insert with key_nr+1. If I succeed, I know the > value of the candidate key. If I hit a duplicate key-nr, I recursively call the > second procedure until I do get a clean key. > > In-Real-Life: John Bossert, Thalatta Corporation, (+1 206.455.9838) John, In a database design which we are currently building all our primary keys use serial values. Due to the current limitation of V5.0, which forgot to allow the extraction of the last serial value used on an insert from SQLCA, we took a different route. The vast majority of our tables have alternative unique keys so what we do is to insert the row with the serial key set to 0. Then we reselect the row using the alternative key to retrieve the serial key assigned to the row. As this all happens in shared memory and within a previously optimised Stored Procedure it takes almost no time. For the tables that have duplicate business keys only we create our own alternate key using an integer field in which we stuff a combination of the unix PID and the datetime. If we get a failure we just retry with a new CURRENT. Lastly we have marked all the reselect code so that it can be replaced with a look in SQLCA for the last serial key inserted when V6.0 becomes available. Hope this helps. Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------