|
|
Posted by Erland Sommarskog on 06/19/06 21:55
(newtophp2000@yahoo.com) writes:
> Since SQL Server has no sequence generator, I wrote my own. (I claim
> no ownership of it as it is closely modeled after earlier discussions
> on this topic.) I have included the sql statements below.
>
> While it works independently on its own, It seems to lock in multi-user
> environments or in nested-transactions. It s funny really: I have my
> main transaction, but the sequence generator below forces another
> transaction, which I do not really care for. I cannot remove the extra
> transaction from the sequence generator because I would like to discard
> any values it retrieved, regardless of whether the main transaction
> succeeded or failed.
>
> --------------------------------------------------------------------------
> ------
> create table my_sequence (name varchar(10), seq int identity (1, 1))
> go
>
> declare @next int
> begin transaction
> update my_sequence set seq = seq + 1 where name = 'abc';
> select @next = seq from my_sequence where name = 'abc';
> commit transaction
That should not even work; you cannot update an IDENTITY column.
As for the nested transaction, all that happens when you commit a
nested transaction is that the transaction counter is decremented.
No locks are released, and nothing is committed for real.
Itzik Ben-Gan had an article on this some issues back in SQL Server
Magazine. I seem to recall that he suggested a sequence generator
that went something like:
SAVE TRANSACTION seq
INSERT tblwithidentity DEFAULT VALUES
SELECT @seq = @@identity
ROLLBACK TRANSACTION seq
The point here is that since sets up a save point and rolls back, the
table remains available, and other processes are not blocked. At the
same time, you know that you will not get the same value twice,
because the generation of the IDENITTY value is not rolled back.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|