Create a counter

I have to create a counter (it is possible to increase or decrease).
It is not record ID, but a part of an automatic generated name.
In case of intensive concurrent use I have a deadlock.
The steps:
1. Launch transaction with isolation level REPEATABLE READ (or SERIALIZABLE
in respect of finish are equal)
2. open the recordset
3. get old number
4. set new number
5. update
6. close record set
7. commit
If I run my program on three computer on the same database a deadlock occur.
How can I isolate the sessions without exceptions?
How can I qued up my clients for its new counter?
I am glad of any idea.
Regards,
Imre Ament
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
>
Yep that's a recipe for a deadlock. Each session gets and holds a read lock
on the table, then each tries to escalate it to an update lock. You need to
get the update lock when you first open the recordset. Then your clients
will serialize properly.
To get an exclusive or update lock with a select statement look at the
UPDLOCK and XLOCK hints.
David
|||Never do something like this from the client, always use a stored procedure
and minimize the locking. How about doing something like this instead:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:F59F319C-190C-4B80-BEAF-FAEA4CFD7E91@.microsoft.com...
> I have to create a counter (it is possible to increase or decrease).
> It is not record ID, but a part of an automatic generated name.
> In case of intensive concurrent use I have a deadlock.
> The steps:
> 1. Launch transaction with isolation level REPEATABLE READ (or
SERIALIZABLE
> in respect of finish are equal)
> 2. open the recordset
> 3. get old number
> 4. set new number
> 5. update
> 6. close record set
> 7. commit
> If I run my program on three computer on the same database a deadlock
occur.
> How can I isolate the sessions without exceptions?
> How can I qued up my clients for its new counter?
> I am glad of any idea.
> Regards,
> Imre Ament
>