Updating a frequently used table
-
Hi all:
I have a table whose values (almost all rows) will be frequently updated (once in 5 mins). However the values in the table are also frequently used. So I must ensure that the table's values arent locked (except while updating). When I do the entire updating action in a transaction I find that the table gets locked most of the times till the transaction is complete. I cannot have one transaction for each update as they should either happen all at once or none at all. Im using ADO objects over SQL server. How can I implement the above solution efficiently with minimum table locks? Please advice.
-
SQL Server handles locks internally.
How many rows in the table need to be updated ? It all depends on the rows to be updated, the table will be locked till that time.
If you can post your transaction block (sql statements), we will be able to guide you better.
--Kishore
-
Do you mind dirty reads?
If you don't mind use the WITH NOLOCK query hint on the SELECT statement.
It means that the readers might see obsolete values while the update is taking place, but if the update takes place every 5 minutes then this shouldn't be an issue.
-
I just hope the table is small enough
* Noel -
Or you can use the WITH ROWLOCK table hint in your update statement but be aware: It will hinder the performance!
Especially if, as are are stating, you are upgrading nearly your entire table.
In such case a table lock is needed for an optimal performance. It is up to you how you want to handle your data: dirty read or row lock or the select statements will wait until the update statement is finished
Bye
Gabor
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply