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:356191
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
