Index on sessionid will help immensely with that many rows. Ultimately, I would suggest never using a Guid as a primary key, performance is going to be less than ideal, even with an index.
Byron Mann Lead Engineer & Architect HostMySite.com On Jul 16, 2013 1:02 PM, "Brook Davies" <[email protected]> wrote: > > Hey Guys, > > I am getting a lot of these errors today: > > "Transaction (Process ID 65) was deadlocked on lock | communication buffer > resources with another process and has been chosen as the deadlock victim. > Rerun the transaction." > > On a system that is used to track 'views' of a page via an ajax request. > All > of the errors are related to updating a specific table with a query like > this (this is not the CFML query..): > > update formAnalytics_view > set INLINESTART={ts '2013-07-16 10:04:21'} , > INTERACTED=1 > where sessionid = '5BAC1158-D89E-4CB5-A8FB-1DC06D3E82A9' > > The table has 4,925,131 rows and growing. It is has more writes than reads. > Is there something I can do to optimize this table to avoid these deadlock > errors? > > I had added a try/catch around the transactions that were causing these > deadlocks, and then I was re-running the failed transaction in the catch > segment, but it looks like the lock was not yet removed and this was just > leading to more errors. What is the best way to resolve this? Would adding > a > thread.sleep() statement and then re-trying the transaction work or would > that just lead to more errors? > > Note the queries should 9 times out of 10 be updating different rows of > this > table. Should I add an index on the sessionID? Or would that make writes > slower? > > Brook > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:356192 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
