Client Variable SQL deadlock

[redWebLackey]

Hi, we are having a problem with intermittant (about 20/hour) deadlocks on the
CData table in our Sql Server. When I run a sql trace I see two transactions
attempting to update the same record at exactly the same time.

Here are some details:
We have 11 web servers and traffic is controlled by an F5 load balancer
All machines are CFMX 6.1 with current patches
All machines have the Disable Global Client Variable Updates box checked
Deadlocks do not occur at a predictable interval
Only one machine is set up to purge client variables

Any ideas where I might look? Any help would be greatly appreciated. Thanks.

[byron1021]

This can happen with large tables. If you are keeping the client vars for a
long time period you may want to look at reducing it. If you have multple
apps, break up the client vars into different db's if possible, this will help
overall performance as well. Instead of having one client db for all your
apps. You may want to check the indexes on the tables as well. You can try
reindexing and see if that helps the problem. Also I tend to keep client db's
as simple recovery models. Especially if they are not very critical. You can
try resetting your database connections as well, perhaps there is one 'stuck'
on a query or something that is uncommited.

[dave_jf]

Has anyone found a fix for this? We cleared all the data from the cdata and
cglobal tables and restarted cf. Seconds after this we started getting
deadlocks again. This time there were only 100 rows in the cdata table. We
turned off global updates so that should not be the issue.

Any help on this would be greatly appreciated.

Thanks,

--Dave

[redWebLackey]

Hi Dave, not yet but I will reply to the thread if anything comes up.

[dave_jf]

I would love to hear from MM on this. We are getting deadlocks at the rate of
about 2 a minute. Even after clearing the cdata & cglobal tables. It is
causing a major performance issue with our database.

--Dave

[redWebLackey]

Indeed. I may have stumbled onto something today. It's a little hard to explain
because it's sort of a messy situation but basically one directory
(non-fusebox) has an application.cfm file and it uses cfmodule or cfinclude to
execute code in another (fusebox) directory. It seems that since the fusebox
directory executes the cfapplication tag in a file other than application.cfm
it is creating the double client variable updates. I hope that made sense. :)
I haven't come up with a resolution to our problem yet but I thought this may
give you something to look at anyway.

[dave_jf]

I have something a little similar but it should not be updating the client vars on every request.

[redWebLackey]

What I've seen from the sql traces that I've run is that cf runs an update for
every page request whether you set a client variable or not. We dedicated a sql
server to the client variables because if you have other long-running queries
they will impact the client variable updates as well.