Re: CF8 SQL Deadlock
On Thu, May 28, 2009 at 2:18 PM, Andy Gillette wrote: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 87) was deadlocked While looking in the FusionReactor logs, I am noticing that this seems to be only happening when the Finished Request Count in FR is above CF's Maximum number of simultaneous Template requests (which is set to 60). I am guessing (that is what I need clarification on) that this is what is giving us the deadlocks? I think you are confusing cause and effect. Most likely you have a deadlock, requests queue up behind the deadlock and when the deadlock is resolved by rolling back one of the transactions involved all the queries in the queue finish immediately and cause the spike in the finished request count. Should we be using CF Enterprise? This is not a CF problem but a database problem. Since you haven't provided any details on the database we can only provide general guidance: 1. MS SQL Server has an option to log the SQL statement of all the transactions involved a deadlock. Use that to see which statement is the deadlock winner and figure out what the resources under contention are. 2. MS SQL Server by default uses a transaction isolation level that causes writes to block reads and vice versa. Since version 2005 snapshot isolation is available, which doesn't do predicate locking so only writes block writes (at the cost of moving from mathematical serializability to the limited serializability defined in the SQL standard). Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322943 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
CF8 SQL Deadlock
Hello, I have read just about everything I can (searched just about all forums, adobe, etc) and can't find an answer. We are running CF 8 Standard and FusionReactor 3 and have a heavy load application running but we are starting to get SQL deadlocks. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 87) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. brThe error occurred on line 115. This isn't happening on a particular cfm page but random. While looking in the FusionReactor logs, I am noticing that this seems to be only happening when the Finished Request Count in FR is above CF's Maximum number of simultaneous Template requests (which is set to 60). I am guessing (that is what I need clarification on) that this is what is giving us the deadlocks? Should we be using CF Enterprise? Any help would be greatly appreciated. Thank you Andy ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF8 SQL Deadlock
Hello, I have read just about everything I can (searched just about all forums, adobe, etc) and can't find an answer. We are running CF 8 Standard and FusionReactor 3 and have a heavy load application running but we are starting to get SQL deadlocks. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 87) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. brThe error occurred on line 115. This isn't happening on a particular cfm page but random. While looking in the FusionReactor logs, I am noticing that this seems to be only happening when the Finished Request Count in FR is above CF's Maximum number of simultaneous Template requests (which is set to 60). I am guessing (that is what I need clarification on) that this is what is giving us the deadlocks? Should we be using CF Enterprise? Any help would be greatly appreciated. Thank you Andy Forgot to mention, we are not using CFLOCK anywhere. Thanks ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322859 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
