Actually, lock (in counter table) is requried if you want to get 100% unique value for primary key immediately. However, if you prefer to beat table down until you will really get correct key, i.e. get first one, try to update, trap the error if not unique, get next value, update again etc. It will also work, why not, but what the purpose? Locking is not really bad, and SQL-Server also uses it, behind the scene most of times.
Do not take me wrong. If it works for you, that's good, but in reality there is nothing to prove here.

>Can either of you please support your claims? I am not locking, because I don't need to lock. Locking takes time, locks get left in place, locks cause things to wait, and locks are not as portable to an SQL server. (I don't necessarily want to use an identity field.) Also, algorithms that use locks need to flip the alias around, and you then run the risk of it not getting flipped back to the right one.
>
>So, do you guys still think it can fail? If so, please show steps.
>
>
>>This is for SINGLE USER only. In multiuser environment you would have to lock the highest key until after you inserted and updated the new record.
>>Why don't you just have a primary key table with tablename C and nextid I fields, look up the tablename you want to append to, lock it, add one to nextid and unlock it and then use this as the primary key for your data table.
>>Peter
>>
>>