Hi all :
I got a problem here.

The scenario :
The serial key code of a table is auto generate, and currently I'm using MAX to return the latest serial key code. The problem is, if the table grow bigger and access by multiple user at one time ( I think select statement does not have any row or table lock) the MAX key will return the wrong serial key code or even fail at some point. The serial key code is the only unique key in the table.

Can anyone give me some guide or tips on how to resolve this? Or other way of implementing the select statement to avoid usage of MAX?

Thanks in advance.

the best way to avoid this is not to return the latest serial key at all

what do you need it for?

I need to get the serial key code as a foreign key to insert into another table. The Serial key code will server as a customer ID in my case. since no customer ID can be and should be duplicate, I need to get the serial key code.

I had figure out a way, using a select * statement and use a while loop to loop thru the whole result set collection. From there I can get the last serial key code.

I got a question here, if I use this way, will it be less effective than using MAX? Consider I got a table with more than 5000 record.

Thanks.

On the table you're inserting into, will the SerialKeyCode be unique, or will you have a one-to-many relationship?

Serial Key Code will be generate automatically by the database and been set to unique with increament by 1 everytime new record is add.

If you return the maximum serial ID at 14:00 and then again at 14:01 they may indeed be different. I don't see a problem. If you wish to keep an accurate record, then you could implement an insert trigger to store the most recent serial code in another table.

here's what you do --

insert a row, and let the database automatically assign the key

use the provided database function to retrieve the value of the key that was assigned

in SQL Server, this is the @@identity function, in mysql it is mysql_insert_id(), use whatever function your database provides

if your database does not provide such a function, then simply query back the row that you just inserted, not with MAX but with the values of the other columns

then use the value of the key as the foreign key when you insert the related record

for example, if you insert a username/password combination of 'fred','sesame' into a user table, then you instead of selecting MAX or (shudder!) returning the entire table to see the last one, just run this query:

select userid from users where username='fred' and password='sesame'

the automatically incremented userid value that you get back will be what you use to insert the related row in the other table

Thanks for your suggestion. The problem is that there r no unique key in the table except the auto serial key code assign by the database. I can not use the second method u suggest. About the first suggestion, it's great, but only if I know what kind of statement the databse provide for me to get the number assign. I'm using Informix. It would be great if you can give some hints on the command to use to get the sequence number. Thanks

ah, informix

look up NEXTVAL and CURRVAL in the manual

the serial number is actually separate from the table, and you use NEXTVAL to get the next value that you insert into the table

The process is like this :

1)Insert into table A
2)Get the result set from table A (for the latest serial key code)

What you try to said is that I need to use NEXTVAL before step 1 to get the serial key code I need.

I'm using Java. I need to write SQL statement for NEXTVAL or in the Java Code.

Would be appreciate if you can give any hints. TY.

Insert into tableA values (seq.nextval, column1, column2)
select * from tableA where id = seq.currVal