provocativedating com - Sql server lock table while updating

Cheers CREATE TABLE iwan_test ( pk int not null primary key, cnt int ) CREATE PROCEDURE sp_iwan_update_stats (@pk INT) AS BEGIN set nocount on begin UPDATE iwan_test SET cnt = cnt 1 WHERE pk = @pk IF @@ROWCOUNT = 0 BEGIN TRY INSERT INTO iwan_test VALUES (@pk, 1) END TRY BEGIN CATCH if ( @@ERROR = 2627 ) -- pk violation UPDATE iwan_test SET cnt = cnt 1 WHERE pk = @pk END CATCH end END EXEC dbo.sp_iwan_update_stats 1 EXEC dbo.sp_iwan_update_stats 1 and up) That code is a little risky since there is no transaction defined you can get some strange results.

Say for example you start throwing in deletes randomly, you may get a situation where you will miss a count.

Both of these methods exhibited very similar performance characteristics during my testing.

Keep in mind that in a production system they may perform differently.

Basically I need to select rows from 1 table and conditionally insert or update them in another table to achieve parity.

It seems like I need a loop to do that, but I have read that loops in Sql are ill-advised.

The result of this is that we have created a critical section in our transaction.

Comments