-
how to lock the row or table when update?
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
-
Use update lock to hold lock for the duration of the transaction.
Select max(certificateNumber) from product_table WITH (UPDLOCK) where certificateNumber<> 8888888
-
skhanal , tell you one interesting thing
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
-
This is listed in sql books online.
-
Is there a reason why you can't simply use an identity field?
-
Skhanal, it is automatically released after update?
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
-
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
-
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.
-
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.
-
for the identity issue
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 & "'"
-
No, UPDLOCK wouldn't block you from reading. It would only block you from performing other inserts or updates
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|