Problems with duplicate data

I have the following table:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I am doing the following from a stored procedure:
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
I am trying to create 2 unique values here. One is the JobID, which is an
identity field. and an ApplicantID which is just a calculated field.
Normally, this works fine.
But I have found one place where I have 2 records with the same JobIDs
(3736) as well as the same ApplicantID (1013).
Not sure how that happened.
Is if possible for 2 Inserts to grab the same ApplicantID at the same time?
You could fix this (I assume but setting this up as a transaction to lock
the record/table), but I was under the assumption that the Insert does this
implicitly for the duration of the Insert.
Am I wrong here?
Thanks,
TomIn READ COMMITTED isolation level with non-exclusive locking, it is very
possible for two users to get the same value. The two calls would have to
be made almost simultaneously, but if they both read before one starts the
insert process you will get locks.
Here is ~ what each does
Get shared lock on row
READ value
Release shared lock on row
Get Update lock on row
UPDATE row
Release lock on row
So you need to change it to SET ISOLATION LEVEL REPEATABLE READ (so locks
aren't released) and put XLOCK as a hint on that table in the select so no
one else can read.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23hgL1CJYFHA.1092@.tk2msftngp13.phx.gbl...
>I have the following table:
> CREATE TABLE [dbo].[Applicant] (
> [ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ApplicantID] [bigint] NOT NULL ,
> [PositionID] [int] NOT NULL ,
> [JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
> [DatePosted] [datetime] NULL ,
> [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I am doing the following from a stored procedure:
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> I am trying to create 2 unique values here. One is the JobID, which is an
> identity field. and an ApplicantID which is just a calculated field.
> Normally, this works fine.
> But I have found one place where I have 2 records with the same JobIDs
> (3736) as well as the same ApplicantID (1013).
> Not sure how that happened.
> Is if possible for 2 Inserts to grab the same ApplicantID at the same
> time?
> You could fix this (I assume but setting this up as a transaction to lock
> the record/table), but I was under the assumption that the Insert does
> this implicitly for the duration of the Insert.
> Am I wrong here?
> Thanks,
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23kZLjiJYFHA.3732@.TK2MSFTNGP10.phx.gbl...
> In READ COMMITTED isolation level with non-exclusive locking, it is very
> possible for two users to get the same value. The two calls would have to
> be made almost simultaneously, but if they both read before one starts the
> insert process you will get locks.
> Here is ~ what each does
> Get shared lock on row
> READ value
> Release shared lock on row
> Get Update lock on row
> UPDATE row
Why are we using an Update lock? Is that the lock that is always used for
an insert?

> Release lock on row
> So you need to change it to SET ISOLATION LEVEL REPEATABLE READ (so locks
> aren't released) and put XLOCK as a hint on that table in the select so no
> one else can read.
Where do I set this?
Would doing a "begin tran" also solve the problem?
Thanks,
Tom
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23hgL1CJYFHA.1092@.tk2msftngp13.phx.gbl...
>|||Inline comments:
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:edXahoJYFHA.2560@.TK2MSFTNGP10.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%23kZLjiJYFHA.3732@.TK2MSFTNGP10.phx.gbl...
> Why are we using an Update lock? Is that the lock that is always used for
> an insert?
Actually what I think technically happens is the UPDATE Lock signals that we
might make an update, then an Exclusive lock is taken after the update and
released after the update is committed.
>
> Where do I set this?
>
SET ISOLATION LEVEL REPEATABLE READ
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant (XLOCK)
where ClientID = @.ClientID

> Would doing a "begin tran" also solve the problem?
I would include the BEGIN and COMMIT TRANSACTION statements just to remind
yourself of what is happening, and it will not affect performance.
But, because your statement is already within a transaction since the INSERT
and SELECT are part of the same statement. The problem is that shared locks
are compatible, meaning two different connections can have shared locks on
the same resource, in this case your row with the max(applicationId). This
is why we use the exclusive lock. Then, in READ COMMITTED locks aren't
held, so we up this to REPEATABLE READ, which doesn't allow data to be
deleted out from under us, which it does by not releasing locks (which is
our goal.) Now you can be sure that you won't get collisions.

> Thanks,
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uxAeq4MYFHA.796@.TK2MSFTNGP10.phx.gbl...
> Inline comments:
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:edXahoJYFHA.2560@.TK2MSFTNGP10.phx.gbl...
> Actually what I think technically happens is the UPDATE Lock signals that
> we might make an update, then an Exclusive lock is taken after the update
> and released after the update is committed.
> SET ISOLATION LEVEL REPEATABLE READ
> INSERT INTO Applicant
> (ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
> Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant (XLOCK)
> where ClientID = @.ClientID
>
> I would include the BEGIN and COMMIT TRANSACTION statements just to remind
> yourself of what is happening, and it will not affect performance.
> But, because your statement is already within a transaction since the
> INSERT and SELECT are part of the same statement. The problem is that
> shared locks are compatible, meaning two different connections can have
> shared locks on the same resource, in this case your row with the
> max(applicationId). This is why we use the exclusive lock. Then, in READ
> COMMITTED locks aren't held, so we up this to REPEATABLE READ, which
> doesn't allow data to be deleted out from under us, which it does by not
> releasing locks (which is our goal.) Now you can be sure that you won't
> get collisions.
>
So the XLOCK is used because a lock isn't normally done on the read part of
the statement. Since we could both read before the lock is instituted for
the insert, we are telling the system to lock the "applicant" table before
doing the select. I assume the lock would then be carried through the whole
statement (select then insert) and be released on exit of the insert
statement.
Just reiterating what you already said to make sure I understand what is
happening.
Thanks,
Tom
>|||> So the XLOCK is used because a lock isn't normally done on the read part
> of the statement. Since we could both read before the lock is instituted
> for the insert, we are telling the system to lock the "applicant" table
> before doing the select. I assume the lock would then be carried through
> the whole statement (select then insert) and be released on exit of the
> insert statement.
Close. The lock is usually taken (so noone will invalidate the row while
SQL Server fetches the data) but not usually held. The lock taken is
usually a SHARED lock, but if we use a shared lock, then more than one
process can read the value at the same time. The exclusive lock means NO
ONE can read or modify the data.
The isolation level tells SQL Server to keep the lock until the transaction
is over.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eu0I%23lgYFHA.3164@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uxAeq4MYFHA.796@.TK2MSFTNGP10.phx.gbl...
> So the XLOCK is used because a lock isn't normally done on the read part
> of the statement. Since we could both read before the lock is instituted
> for the insert, we are telling the system to lock the "applicant" table
> before doing the select. I assume the lock would then be carried through
> the whole statement (select then insert) and be released on exit of the
> insert statement.
> Just reiterating what you already said to make sure I understand what is
> happening.
> Thanks,
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uhvb4njYFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Close. The lock is usually taken (so noone will invalidate the row while
> SQL Server fetches the data) but not usually held. The lock taken is
> usually a SHARED lock, but if we use a shared lock, then more than one
> process can read the value at the same time. The exclusive lock means NO
> ONE can read or modify the data.
> The isolation level tells SQL Server to keep the lock until the
> transaction is over.
>
On the isolation level, you said to do the following (I assume in my SP),
SET ISOLATION LEVEL REPEATABLE READ
INSERT INTO Applicant
(ClientID,ApplicantID,PositionID,FirstNa
me,LastName,Email,DatePosted)
Select @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant (XLOCK)
where ClientID = @.ClientID
Is this Level held until I am finished with this connection (SP) or do I
need to change it (and how do I) right after the insert statement?
Thanks,
Tom

> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eu0I%23lgYFHA.3164@.TK2MSFTNGP09.phx.gbl...
>