I have the following table and am trying to enter / select visitor IPs in a high concurrency scenario.
CREATE TABLE [dbo].[stat]( [stat_id] [int] IDENTITY(1,1) NOT NULL, [stats_ip] [bigint] NOT NULL, CONSTRAINT [PK_stat] PRIMARY KEY CLUSTERED ( [stat_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [IX_stat] ON [dbo].[stat] ( [stats_ip] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY] GO
Basically, each concurrent task should perform the operations:
1. select the stat_id of the corresponding visitor IP, if such IP exists in table
2. otherwise, insert the visitor IP and return the corresponding stat_id
So whether the IP exists or does not exist, after inserting, the query should return the ID stat_id.
I've tried various approaches, but none of them prevented the deadlocks or from getting an error (for example, the stat_id ID is not being returned correctly).
Any help is welcome
Thanks in advange