This happened a while ago but I thought I would share...
I am not a huge fan of Identity columns because (among others) they promote laziness in the design phase (or should that be daze!)
However, we found that in one particular case, it was a god send..
We have a touch screen application that is used through out the country and to track its usage (and hence give us feedback on interface use, performance etc...) we setup a table that records what machine is in use and what page is being viewed. It's a Windows Form App (C#) with a front-end that would make da Vinci proud...I was one of the developers who was working on it. (I didn't design the graphical interface.. I'm color blind)....
To perform logging, we spawned a thread to call a web service and just continued on front-end processing. The logging table was setup by the DBA's at the time and the key used was a composite of MachineID, Screen and Time. Now I know that DateTime has a resolution of about 3 milliseconds and my university days told me that a human being has a nerve response of about 20 milliseconds. With that information, you would think it would be impossible to go from Screen A to Screen B and back to Screen A in under 3 milliseconds using your finger....
All is going well in testing until it comes to the "rattle" test. Basically you walk up to the screen and just start tapping as fast as you can with all you fingers (and in a couple of instances your head as well!)....
All of a sudden we start getting key violations from the logging table.
The culprit turned out to be the threading itself. Because a thread starts whenever it wants, we found that the web service call for logging was getting called simultaneously by multiple threads and hence the duplicate values.
A meeting took place to find a solution and these where the options..
- Expand the key
- Introduce a message filter to reject fast tapping responses.
- Screw around with threads in the app
- Add an Identity column and expand our logging reporting to cope with duplicates
- Sink the error
- Alter the Stored Proc to perform an existence check first
We quickly rejected 1, 3, 5, 6
Option 2 was implemented (for other reasons as well) but we still couldn't be sure that the same thing wouldn't happen.
Option 4 was implemented as we are pretty sure (I haven't heard of identity key violations.. has anyone?) that would solve it....
| posted on Thursday, February 19, 2004 12:42 PM