Threads, composite keys and Identity to the rescue

Thu Feb 19, 2004 by byrmol

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….

Legacy Comments


Jay 2004-02-19	re: Threads, composite keys and Identity to the rescue Sounds like interesting work. Did the possibility of syncronization or serialization of the thread(s) ever come up in the discussion as potential options?

DavidM 2004-02-19	re: Threads, composite keys and Identity to the rescue Yeah, in option 3. I am not that thread savy, so I stayed out of that one....

Jay 2004-02-19	re: Threads, composite keys and Identity to the rescue Being a developer first I'm not that database savy. But I must say, with a newly found passion for db theory, that your insightful, and sometimes controversial, posts have inspired me to blow the dust off of my CJ Date books for an intense review. Thanks, and keep the knowlegde flowing.

DavidM 2004-02-19	re: Threads, composite keys and Identity to the rescue No worries... Actually Jay, you might be able to help me out.. I'm building a little Distributed Transaction Conoordinator in C# using the IDBCommand interface and I need help with some threading.. At the moment I simply assign the commands to the ThreadPool.QueueUserWorkItem and pause the main thread using the ManualResetEvent class... Its singled by the last completed thread.. It works OK, but I'm sure an experienced "Threadman" would tear it apart... Interested?

Justin 2004-02-19	re: Threads, composite keys and Identity to the rescue David, Out of curiousity why was option 6 discounted so quickly? It seems like the existence check would solve the problem without bastardizing the key (for lack of a better term) with an identity. Granted I probably would have started right off the bat with an identity but my adherence to relational theory isn't where it needs to be yet.

DavidM 2004-02-19	re: Threads, composite keys and Identity to the rescue Justin, Option 6 was ignored because of expected volume. Between 50K and 250K rows a day. When you think about it a bit, the application changed the requirement of the key itself. A duplicate based on the old key is not really a duplicate, in as much as the screen was actually visited twice. In the reports that we generate, a special section is dedicated to them.. It has turned out to be a very good indicator of when users actual perform there own "rattle" test. You be surprised how many people do!!

Patrick 2004-02-20	re: Threads, composite keys and Identity to the rescue I'd be interested to see some screenshots of your app

secretGeek 2004-02-20	re: Threads, composite keys and Identity to the rescue this might sound blasphemous and i am prepared for flaming, but... if the only way these duplicates are created is by reposting during a rattle test, then couldn't you just have a "real" primary key (not an identity) and if the record fails to insert then you just catch the error and ignore it? i mean, the message has been successfully saved once -- the other was just an ignorable duplicate. by the way -- as someone said above, this blog is a good wake up call, makes devs like me start re-thinking about db design... makes you see the tension between db and dev "best practice" cheers leon p.s. i hope you helped ensure their visual design worked fine for colour blind users!

DavidM 2004-02-20	re: Threads, composite keys and Identity to the rescue Leon, Don't worry, I've taken my happy pills.. Sinking the error was option 5. It was decided that it should not be ignored because it provided valuable information about how often a "rattle" action was performed. As I mentioned in another comment, it happens quite a lot. The hardware for each touchscreen is worth a small fortune and when we see more than 2 "rattles" from the same machine we inform the client that some of their users could be damaging the machine. My vision caused the redesign of 5 screens! Mainly coloured text "disappearing" against coloured backgrounds...

Jay 2004-02-20	re: Threads, composite keys and Identity to the rescue >>that it should not be ignored because it provided valuable information about how often a "rattle" action was performed. Yea, in this case distribution of said happy pills to the user(s) performing the rattle test(s) would be prudent :)

DavidM 2004-02-20	re: Threads, composite keys and Identity to the rescue Jay, The old machines these replaced had a CRT versus the newer LCD... We had a few that where head butted, spewed on and in a famous case, launched out the window from a second story building.. Users.. got to love them!

secretGeek 2004-02-20	re: Threads, composite keys and Identity to the rescue thanks for the response David >We had a few that where head butted, spewed on and in a famous case, launched out the window from a second story building.. ah... reminds me of my university days... regarding: >it should not be ignored because it provided valuable information about how often a "rattle" action was performed. isn't this the kind of stuff you use Event Logs for, rather than hiding it amongst regular data in a regular data table? i know i sound picky, but - well, you've railed against identity columns previously and it really made me stop and think. Now, the only example you've got where you support using an identity column - maybe even this case doesn't really need one. What fate awaits the humble identity column?

DavidM 2004-02-23	re: Threads, composite keys and Identity to the rescue secretGeek, While the EventLog idea is a good one, it would be considered not very practical. We could I suppose set up a process to send the event log back to us and store it in a another table... but as I have said, they aren't considered duplicates anymore...

brad 2004-02-24	re: Threads, composite keys and Identity to the rescue David, how far do you go to avoid using identity column as primary key, and why? I like to use identity column as PK on a good old customer table, is there something wrong with this? Should I aim to use some "real" unique column instead? Why?

DavidM 2004-02-24	re: Threads, composite keys and Identity to the rescue Brad, For me, Identity is the key of last resort. Finding the "real" key is usually very difficult in entities like Customer, Suppliers etc.. so Identity usually makes a good key in those instances. I think you should try as hard as possible to find the "real" key as a database is meant to model reality. They really show up Identity keys when you relationship depth goes beyond about 2 levels. By favourite example is my farm database.. Farm (FarmName) KEY (FarmName) Paddock (FarmName, Paddock) KEY (FarmName, Paddock) Tree(FarmName, Paddock, Row, Position) KEY (FarmName, Paddock, Row, Position) TreeHistory(FarmName, Paddock, Row, Position, Date) KEY (FarmName, Paddock, Row, Position, Date) TreeWork(FarmName, Paddock, Row, Position, Date, Work) KEY (FarmName, Paddock, Row, Position, Date, Work) I have left out non-key attributes, but as you can see, I can perform an enormouse about of queries on the TreeWork table without a single join. If you tacked on an Identity column in each of those tables, even simple queries require lots of joins.... I got in an argument with someone who said this wasn't relational because I haven't used Identity columns and that I had a lot of redundant data. I asked him how can a key be redundant?

Paul 2004-03-06	re: Threads, composite keys and Identity to the rescue I got in an argument with someone who said this wasn't relational because I haven't used Identity columns and that I had a lot of redundant data. I asked him how can a key be redundant? No idea about the identity column argument, but I'm guessing that he's never seen your modeling approach. Before I met a professor who enlightened me, I was also of the opinion that every table needed an identiy primary key column. It was a real eye opener, and the benefits are obvious. The one argument is that, while data is not necessarily duplicated, composite keys can become large compared to an artificial key. In VLD this can be a good enough reason to stick with identity keys.