November 20, 2004 at 3:16 pm
Hello all,
I haven't used the READ UNCOMMITTED transaction isolation level
before, and I was wondering if this would be an appropriate use:
I have an ID table containing ID numbers that are randomly generated
and need to be unique. There is a stored procedure that potentially
generates thousands of these IDs in one execution and inserts them
into the ID table and various other tables. The basic idea is as
follows:
Begin Transaction
While not all IDs generated {
GenID:
@NewID = GenerateID()
If @NewID exists in ID table
GOTO GenID
Insert into ID table
Insert into various other tables
}
Commit Transaction
The problem occurs when the stored procedure is being run by more than
one process concurrently. The check to see whether @NewID exists in
the ID table will block, waiting for the transaction in the other
process to commit.
Would this be an appropriate place to use the READ UNCOMMITTED
isolation level to allow different executions of the stored procedure
to see what the others are writing into the ID table before the
transactions finish? I only really care that the IDs generated are
unique; they're not in sequence or anything like that. Has anyone had
experience with doing anything similar?
November 20, 2004 at 8:38 pm
Yes READ UNCOMMITTED would seem to do as you ask
BOL has this to say
READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply