karstenweber
asked on
SQL Server 2008R2 Concurrency
Greetings,
I’m using SQL Server 2008R2 in its default configuration. Below are three skeleton examples of stored procedures.
Question: How do I ensure that each of these types of stored procedures will run as an atomic operation in a multi threaded environment? Does the default locking ensure that the underlying table cannot change between testing if a value exists and updating that value? Should I always put a TRANSACTION BEGIN / COMMIT TRANSACTION around these types of stored procedures? What’s the best practice approach for handling this?
Thanks,
Karsten
########################## ########## ########## ########## ####
STORED PROCEDURE EXAMPLE 1:
IF EXISTS(SELECT * FROM MyTable WHERE MyId = @MyId)
BEGIN
-- Update
UPDATE MyTable
SET
MyData = @MyData
WHERE MyId = @MyId
END
ELSE
BEGIN
-- Insert
INSERT MyTable
(
MyData,
MyId
)
VALUES
(
@MyData,
@MyId
)
END
########################## ########## ########## ########## ####
STORED PROCEDURE EXAMPLE 2:
DELETE FROM MyTable1 WHERE MyId = @MyId
DELETE FROM MyTable2 WHERE MyId = @MyId
########################## ########## ########## ########## ####
STORED PROCEDURE EXAMPLE 3:
DECLARE @Test1 bit
IF EXISTS(SELECT * FROM MyTable MyId = @MyId)
SET @Test1 = 1
ELSE
SET @Test1 = 0
DECLARE @Test2 bit
IF EXISTS(SELECT * FROM MyTable MyId = @MyId2)
SET @Test2 = 1
ELSE
SET @Test2 = 0
DECLARE @CombinedTest int
SET @CombinedTest = 0
IF @Test1 = 1 SET @CombinedTest = @CombinedTest + 1
IF @Test2 = 1 SET @CombinedTest = @CombinedTest + 2
RETURN @CombinedTest
I’m using SQL Server 2008R2 in its default configuration. Below are three skeleton examples of stored procedures.
Question: How do I ensure that each of these types of stored procedures will run as an atomic operation in a multi threaded environment? Does the default locking ensure that the underlying table cannot change between testing if a value exists and updating that value? Should I always put a TRANSACTION BEGIN / COMMIT TRANSACTION around these types of stored procedures? What’s the best practice approach for handling this?
Thanks,
Karsten
##########################
STORED PROCEDURE EXAMPLE 1:
IF EXISTS(SELECT * FROM MyTable WHERE MyId = @MyId)
BEGIN
-- Update
UPDATE MyTable
SET
MyData = @MyData
WHERE MyId = @MyId
END
ELSE
BEGIN
-- Insert
INSERT MyTable
(
MyData,
MyId
)
VALUES
(
@MyData,
@MyId
)
END
##########################
STORED PROCEDURE EXAMPLE 2:
DELETE FROM MyTable1 WHERE MyId = @MyId
DELETE FROM MyTable2 WHERE MyId = @MyId
##########################
STORED PROCEDURE EXAMPLE 3:
DECLARE @Test1 bit
IF EXISTS(SELECT * FROM MyTable MyId = @MyId)
SET @Test1 = 1
ELSE
SET @Test1 = 0
DECLARE @Test2 bit
IF EXISTS(SELECT * FROM MyTable MyId = @MyId2)
SET @Test2 = 1
ELSE
SET @Test2 = 0
DECLARE @CombinedTest int
SET @CombinedTest = 0
IF @Test1 = 1 SET @CombinedTest = @CombinedTest + 1
IF @Test2 = 1 SET @CombinedTest = @CombinedTest + 2
RETURN @CombinedTest
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the feedback Icohan. I did suspect that the default read commited isolation would suffice, but it wasn't exactly clear. Thanks for the re-write. The original form was pretty brain dead, but did show the multiple select that I was concerned about.
Karsten
Karsten
select case
when EXISTS (SELECT * FROM MyTable where MyId = Id1) then 1
when EXISTS (SELECT * FROM MyTable where MyId = Id2) then 2
when EXISTS (SELECT * FROM MyTable where MyId = Id1) and EXISTS (SELECT * FROM MyTable where MyId = Id2) then 3
else 0 end