3

I have a Classic ASP site using ADODB with the sqloledb provider to connect to SQL Server 2012. When a user clicks on a link, I am inserting a record into a table to track the clicks. The table has a primary key comprised of three columns: id [int], clickTime [datetime2(7)], and remoteAddress [varchar(15)]. My insert statement is providing the id and remoteAddress values, and the clickTime column is set to a default of SYSDATETIME(). An example would be:

INSERT MyClick (id, remoteAddress) VALUES (766319,'108.178.236.50')

I am capturing a few thousand clicks per day, but I am getting a handful of errors each day for duplicate keys. Here is an example:

Violation of PRIMARY KEY constraint 'PK_MyClick_1'. Cannot insert duplicate key in object 'dbo.MyClick'. The duplicate key value is (766319, 2016-01-20 15:30:14.1570772, 108.178.236.50).

With the relatively light traffic, inclusion of the user's IP address in the key, and extreme precision of datetime2, I cannot understand how I am still getting occasional duplicate keys. I understand that I could add an identity or GUID column to the key to guarantee uniqueness. I also understand that I could retry the insert in the event it fails. However, I'd really like to understand this:

How is it possible to get duplicate keys in my scenario?

EDIT: I figured out why the web site was attempting to insert two similar records (for the same id and remoteAddress). It stems from an ill-conceived attempt years ago to get around the problem of pop-up blockers breaking our site. The page had an <a> tag wrapped around a <div> tag. The <a> tag opened the page with a _blank target, and the <div> had an onClick event that used window.open to open the same page in a a new window. This onClick is usually blocked by pop-up blockers, so only one page would be opened as expected. But if the user allowed pop-ups for the site (or used a browser with no pop-up blocking), it would actually open the same page in two different tabs. Both of the pages were trying to insert the database record, and if they hit close enough together, the duplicate key error occurred. What I found in the database was that we have many cases of near-duplicate records separated by a fraction of a second. So if I fix the site to not open the page in two different windows, I believe this duplicate problem will go away (even though it's still technically possible). Thanks to those who offered suggestions to help me figure it out.

Garland Pope
  • 3,242
  • 1
  • 25
  • 19
  • 1
    You may not have 100 NS resolution from `SYSDATETIME`. "The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running." See note: https://msdn.microsoft.com/en-us/library/bb630353.aspx – Shannon Severance Jan 22 '16 at 20:53
  • You can have thousand clients (browsers) behind of the single white IP, so you can have collision. How do you generate Id? – Hamlet Hakobyan Jan 22 '16 at 20:55
  • 2
    Your user is probably clicking to fast, Just do the right thing and create and autonumeric field in your table. Or check your app again to make sure double clicks arent possible. Like a bank you dont want allow user send payment twice. – Juan Carlos Oropeza Jan 22 '16 at 20:56
  • Or maybe the web app submits through javascript and can do it twice in very quick succession in some circumstances so double posting the same info. – Martin Smith Jan 22 '16 at 21:05
  • You could change the primary key to just be `id` and change it to an `IDENTITY` column. Then there won't be any conflicts. You could then add an index that includes `id`, `clickTime`, and `remoteAddress`, if necessary. Sorry, I was trying to solve the problem, but didn't pay attention to what you were actually asking. – gmm Jan 22 '16 at 21:06
  • @HamletHakobyan: id is the key of the record displayed on the target page of the link they clicked. In this case, it's a vacation rental property. So this insert represents a person at IP address 108.178.236.50 clicking a link to view property #766319. – Garland Pope Jan 22 '16 at 21:31
  • @JuanCarlosOropeza: I've considered that it could be a double-clicking issue, but I just can't understand how it could generate two records with a timestamp value so precise as 2016-01-20 15:30:14.1570772. Certainly a human can't click that fast, and even if the two requests are somehow queued up and executed consecutively, I'd be surprised to see a duplicate. – Garland Pope Jan 22 '16 at 21:37
  • There are lot of thing behind user click and send it to the db. All depend on OS, event queues. cpu load, traffic speed. So is possible, and if you didnt take proper precaution more like it to happen. In web page the first thing you program to click event is `button.disable` to avoid double click – Juan Carlos Oropeza Jan 22 '16 at 21:40
  • @JuanCarlosOropeza: My best guess is that you're correct. I suppose the user is clicking twice, the requests are getting queued up due to high traffic, and then they are executed in quick succession by SQL Server, getting the same result for sysdatetime. I see from this question that getting the same result for multiple inserts is not unusual: http://stackoverflow.com/questions/34016556/accuracy-of-sysdatetime-data-type-in-sql-server – Garland Pope Jan 22 '16 at 21:54
  • I just ran the same test as in http://stackoverflow.com/questions/34016556/accuracy-of-sysdatetime-data-type-in-sql-server. I used a loop to insert 100,000 records, and I only had 1,285 unique datetime2 values. I had anywhere from 3 to 128 records with the same value from sysdatetime! So I guess getting an occasional duplicate in my scenario doesn't seem so unlikely. – Garland Pope Jan 22 '16 at 22:19

1 Answers1

3

There are two issues here.

The first is that even with the precision of the datetime2 data type, SQL Server can insert several records with the same value. In a loop inserting 100,000 records with a datetime2 column set to sysdatetime(), I only got 1,285 unique values. There were as many as 128 records with the same value in the datetime2 column, so either my database server is so souped up it can insert records with extreme efficiency or the accuracy of the sysdatetime() function is not quite what one might expect. Either way, you simply cannot rely on sysdatetime() for a unique value.

With that, however, it still seemed very odd that with two other columns in my key (including the user's IP address) I could get duplicate keys. That would indicate that two people with the same IP address happened to be viewing the exact same data (out of thousands of possibilities) at the exact same time. As it turned out, for browsers not blocking popups, the web page being requested was opening in two different tabs at the same time. This usually produced a pair of records in the database with slightly different datetime2 values, but occasionally it was producing the duplicate keys when the two page loads happened close enough together. I corrected the web page, and the duplicates have stopped completely.

Thanks to @JuanCarlosOropeza for his help in thinking through the issue.

Garland Pope
  • 3,242
  • 1
  • 25
  • 19