|
|
Posted by Gert-Jan Strik on 07/10/05 15:44
As mentioned by Erland, you could place your batch in a transaction, and
serialize the action either by settings the TRANSACTION ISOLATION LEVEL
to SERIALIZABLE or to use locking hints, as shown below:
BEGIN TRANSACTION
If NOT EXISTS (
SELECT *
FROM tblRecords (UPDLOCK HOLDLOCK)
WHERE EmployeeNumber = @SocialSecurity
AND ArrivalTime >= DATEADD(day,-45,@ArrivalTime)
)
Begin
INSERT INTO tblRecords(EmployeeNumber,ArrivalTime)
VALUES (@EmployeeNumber, @ArrivalTime)
SELECT SCOPE_IDENTITY()
End
Else
SELECT 0 AS DuplicateRecord
COMMIT TRANSACTION
HTH,
Gert-Jan
Elroyskimms wrote:
>
> I have a table using an identity column as its Primary Key and two
> columns (table reduced for simplicity) EmployeeNumber and ArrivalTime.
>
> CREATE TABLE [tblRecords] (
> [ID] [bigint] IDENTITY (1, 1) NOT NULL ,
> [EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ArrivalTime] [datetime] NOT NULL ,
> CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> I have an insert procedure that checks for duplicates before inserting
> a new record:
>
> IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =
> @SocialSecurity) IS NULL
> BEGIN
> INSERT INTO tblRecords(EmployeeNumber,ArrivalTime)
> VALUES (@EmployeeNumber, @ArrivalTime)
> SELECT SCOPE_IDENTITY()
> END
> ELSE
> SELECT 0 AS DuplicateRecord
>
> In 99.9% of the cases, this works well. However, in the event that the
> insert attempts are literally "ticks" apart, the "SELECT TOP 1..."
> command completes on both attempts before the first attempt completes.
> So I end up with duplicate entries if the procedure is called multiple
> times vey quickly. The system needs to prevent duplicate
> EmployeeNumbers within the past 45 days so setting the EmployeeNumber
> to UNIQUE would not work. I can check for older entries (45 days or
> newer) very easily, but I do not know how to handle the times when the
> procedure is called multiple times within milliseconds. Would a
> TRANSACTION with a duplicate check after the INSERT with a ROLLBACK
> work in this case? Any help is greatly appreciated!
>
> -E
Navigation:
[Reply to this message]
|