PJSen Blog

I have been diagnosing very subtle bug in SQL code which led to unexpected results. It happens under rare circumstances, when you do update with join and you want to increase some number by one. You just write value = value + 1. The thing is, you are willing to increase the value by the number of joined rows. The SQL code kind of expresses your intent. However, what actually happens is, the existing value is read only once. It is updated 3 times, indeed. But with the same value, incremented only by one.

	declare @UpdateTarget table(Id bigint, Capacity int);
	insert into @UpdateTarget (Id, Capacity) values (10, 0); -- Lets assume this is our counter of available resources
	declare @HowManyTimesUpdate table(TargetId bigint);
	insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
	insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
	insert into @HowManyTimesUpdate (TargetId) values (10); -- Lets assume this is a reservation of a resource
	-- Now, we are releasing resources. Increase the counter for those 3 reservations
	update ut
	set ut.Capacity = ut.Capacity + 1
	from @UpdateTarget ut join @HowManyTimesUpdate hmt on ut.Id = hmt.TargetId;
	-- We expect result 3, because 3 resources should be released, but there is only 1
	select Capacity from @UpdateTarget;
	-- Reset and start again
	update @UpdateTarget set Capacity = 0;
	-- Do it right
	update ut
	set ut.Capacity = ut.Capacity + (select count(1) from @HowManyTimesUpdate where TargetId = [Id])
	from @UpdateTarget ut join (select distinct TargetId from @HowManyTimesUpdate) hmt on hmt.[TargetId] = [Id];
	-- We expect 3, there is 3
	select Capacity from @UpdateTarget

view raw blog-2018-03-29-sql1.sql hosted with by GitHub