November 5, 2003 at 7:55 am
Hey..
What happens with code like this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
INSERT
ATable (AColumn)
SELECT
AColumn
FROM
BTable WITH (NOLOCK)
JOIN CTable WITH (NOLOCK)
ON BTable.KEY = CTable.KEY
IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('A Insert Error', 16, 1)
END
How does the SERIALIZABLE statement interact with the WITH (NOLOCK) statements ???
Any help is appreciated.
- B
November 5, 2003 at 8:11 am
For clarity - here is my real question.
The CTable is a BIG table that is Never edited. So, I don't want to lock that table at all if I don't have to. BUT, I want the whole transaction to be stable, thus SERIALIZEABLE... I'm confused.
- B
November 5, 2003 at 9:12 am
This is what BOL says about SET TRANSACTION ISOLATION LEVEL
quote:
Only one of the options (ISOLATION LEVELS) can be set at a time, and it remains set for that connection until it is explicitly changed. This becomes the default behavior unless an optimization option is specified at the table level in the FROM clause of the statement.
...
so your source tables won't be Locked but you destination will.
HTH
* Noel
November 5, 2003 at 10:32 am
This may be a naive question.... Why not just remove the NOLOCK hint from BTable so it gets held in a transaction. Seems like you wouldn't need to worry about the Isolation level setting at all.
Guarddata-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply