Archive for T-SQL

You are browsing the archives of T-SQL.

T-SQL Race Conditions (and how to avoid them)

I’ve been writing T-SQL stored procedures for many years now, and I never fully understood how the concurrency of multiple users truly affects the asynchronous operation of certain types of paradigms until now.
Take the following T-SQL:
SELECT @ID = ID FROM tblTable WHERE nvhSomeText = ‘match’
IF @ID IS NULL
INSERT INTO tblTable (nvhSomeText, intSomeNumber) VALUES (‘match’, 10)
ELSE
UPDATE [...]

DECLARE CURSOR … FOR UPDATE

Just a quick post to basically document somewhere the problem I was getting and the solution I found.Take the following cursor declaration:
DECLARE cur_Columns CURSOR LOCAL
FOR SELECT DISTINCT person_id
FROM Company_People
WHERE company_id = @company_id
FOR UPDATE OF person_id
There is no obvious reason to me why this should not work, but attempting to use this cursor for updating a column will fail with [...]