how to lock the row or table when update?

[920506]

Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.

cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then

certnumber=cert_info(0)+1

End if
cert_info.close
set cert_info=nothing

[skhanal]

Use update lock to hold lock for the duration of the transaction.

Select max(certificateNumber) from product_table WITH (UPDLOCK) where certificateNumber<> 8888888

[920506]

Skhanal,
Thank you so much. The very interesting
thing is when I search in the google.
There are a lot of article about concept sharelock, updatelock etc., I just got a hint I need to use update lock, but I just didn't find an easy sample there.
Thanks for your valuable posting and it save me a lot of time.
Betty

[rmiao]

This is listed in sql books online.

[Rawhide]

Is there a reason why you can't simply use an identity field?

[920506]

Skhanal,
following the code junk in the first posting. I have the following code which I check if the new record is in the database,if not, I add a new record with the new certificate number I just got.
/***********
cmdTemp.CommandText = "select * from Product_table where order_id='" & order_id & "'"
Set order_info = Server.CreateObject("ADODB.Recordset")
order_info.Open cmdTemp, , adOpenKeyset, adLockOptimistic
if order_info.EOF then
order_info.AddNew
end if

order_info("CertificateNumber").Value=certnumber
...
'fill with other field data
order_info.update
/********
Now the question is: will the second select statement from the product_table will still execute. Is the lock going to be released after the line order_info.update? thanks
Betty

[920506]

Rawhide,rmiao
I think you mean the priamry ID key.
we have very specific requirements for our certificate number. If I want to use Identity field, I need to reset seed for the identity field. I am just a developer, I just don't know much about database. Usually don't want to touch which I don't know much.
rmiao, would you send me the link for this issue. I want to read more.
Thanks
Betty

[Rawhide]

This method will cause you some serious performance issues it is a moderately busy site. While you are locking the table, other queries will build up in the queue. At the very least, you will experience timeouts. At the worst, deadlocking.

[Rawhide]

A field doesn't have to be the primary key to be an identity field.

You should only need to set the seed and increment value once for an identity field.

[920506]

Rawhide,
The problem is the new identity is known after the insertion of the record.
while I need this certificate number to write to another document before the insertion.
By the way, do you mean after
the statement:
Select max(certificateNumber) from product_table WITH (UPDLOCK) where certificateNumber<> 8888888
I cannot have the following statement:
cmdTemp.CommandText = "select * from Product_table where order_id='" & order_id & "'"

[Rawhide]

No, UPDLOCK wouldn't block you from reading. It would only block you from performing other inserts or updates