|
|
Posted by Hugo Kornelis on 10/17/07 20:42
On Wed, 17 Oct 2007 00:08:48 -0700, Nick Chan wrote:
>hello guys, need help, i;m a bit confused about transactions after
>reading some articles about locking/snapshot
>
>
>let's stay i have this scenario
>
>
>-----------------------------------------------------------------------------------
>begin Tran 1 |
>select Max Id = 5 |
>set @a = MaxId + 1 = 6 |
>insert into t values(@a) |
>-----------------------------------------------------------------------------------
> | begin Tran 2
> | select max Id = XX
> | set @a = maxId + 1 = ?
> | insert and commit tran
>2
>-------------------------------------------------------------------------------------
>commit tran 1 |
>------------------------------------------------------------------------------------
>
>what will XX be ?
Hi Nick,
Assuming the illustration above means what I think it means (getting a
max value from a table, increasing it by one and then using that value
in an insert), the answer depends on your transaction isolation level.
Read committed (the default), repeatable read, and serializable:
If the timing is exactly as depicted above, the second transaction will
wait for the first transaction to commit before the select statement
finishes. The net effect is that two rows are inserted, with values of 6
(from tran 1, inserted first) and 7 (from tran 2, inserted second).
If the timing changes slightly and the second tran manages to squeeze in
its read BEFORE the first transaction gets around to inserting a new
row, you WILL get a deadlock for read committed, you MIGHT get either a
deadlock or the same blocking as described above (depending on indexes
available) for repeatable read, and you will get blocking for
serializable.
Read uncommitted (aka dirty read or nolock):
The select in the second transaction will run without waiting for the
first transaction to finish, reading the "new" maximum. The insert in
the second transaction might or might not wait for the first, depending
on indexes available. So you will get two new rows, with values of 6
(from tran 1, inserted first OR last, depending on indexes) and 7 (from
tran 2, inserted last OR first).
In case you think that this is just what you need think again. If the
first transaction does a rollback instead of a commit, you will ONLY get
the row with a value of 7, and the row with value 6 will never have
existed. If this is acceptable to you, then you should just go ahead and
use IDENTITY instead of rolling your own.
Snapshot (SQL Server 2005 only):
The select in the second transaction will run without waiting for the
first transaction to finish, but it will read the "old" maximum. The
insert in the second transaction might or might not wait for the first,
depending on indexes available. So you will get two new rows, both with
a value of 6. I really don''t think that this is what you're after!
> I thought transactions run in queues, but i saw
>illustrations like the above
No, SQL Server can handle many concurrent transactions. It really would
no scale well if a database that serves hundreds of users simultaneously
would have to put all requests on hold if a manager starts a
long-running query! :-)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|