One of our production sites started throwing the below error and occasionally was recycling the Application Pool
Exception Details
[sourcecode language=”xml”]
Exception: System.Data.SqlClient.SqlException
Message: Transaction (Process ID 3866) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
at Sitecore.Analytics.Data.DataAccess.DataAdapters.SqlBase.ReadMany(DataSourceQuery query, Action`1 handler)
at Sitecore.Analytics.Data.DataAccess.DataAdapters.Sql.SqlLookup.GetVisitsWithEmptyLocation()
at Sitecore.Analytics.Lookups.GeoIpWorker.ProcessVisits()
at Sitecore.Analytics.Lookups.GeoIpWorker.<Wakeup>b__3(Object a)
[/sourcecode]
After some research , We found that the SQL Deadlock was occurring due to multiple production servers were performing the GeoIP lookup using maxmind webservice.
This caused 2 issues on our site
- Application pool was getting recycled and the above exception were thrown
- Our Maxmind Queries were getting consumed at a very high rate
Resolution
- Disable GeoIP lookup on all the production (CD) servers by setting the below value in the Sitecore.Analytics.config file.The below setting should be set to TRUE on only one server (for example : least loaded server, as a rule – CM server)
- We also configured the Auto Detection of BOTS in our Analytics configuration so that it does not consume a lot of queries , Analytics.AutoDetectBots: This setting is present in the Sitecore Analytics File and ensures that the auto detection of BOTS is enabled
- Ensure that your Sitecore solution has this include config file enabled (Sitecore.Analytics.ExcludeRobots.config), It contains list of user agent strings that are used by search engines. Requests with following user agent strings will be ignored by Sitecore Analytics and will not be processes.The above analytics file also contains a section called excludedIPAddresses, this setting allows you to specify a range of IP addresses and the requests originating from those IP addresses will be ignored by Sitecore Analytics.
[sourcecode language=”xml”]
<setting name="Analytics.PerformLookup" value="false" />
[/sourcecode]
[sourcecode language=”xml”]
<setting name="Analytics.AutoDetectBots" value="true" />
[/sourcecode]
NOTE: This website http://iplists.com/ keeps a list of up to date IP address of search engines.so it would not hurt to check the website for periodical updates to ensure that the IP addresses are up to date
Summary
Once we did the above changes, everything was back to normal and it also greatly reduced our consumption of maxmind web service queries
Should you have any questions, Please reach me on twitter @sjain_hi or email me at sjain@horizontalintegration.com