We are using Moodle 2.9.1 in Windows environment.
Web application is hosted under IIS - virtual machine with 32 GB of RAM and 4 CPU cores. CPU usage usually around 5-15%. It also hosts several other active applications.
Microsoft SQL Server is used for database - physical server with 128 GB of RAM and 64 cores (core count as shown in Task Manager, probably 32 physical cores due to hyper-threading). Note that this SQL Server hosts several other large and really active databases none of which experience any deadlocks.
Moodle cron job is setup and the highest number of rows in MDL_SESSIONS table I observed was about 150, so the table is really small (MDL_ is prefix chosen during Moodle installation).
As I can see MDL_SESSIONS table is indexed by most of the columns.
The problem we are facing is fairly frequent deadlocks on MDL_SESSIONS table even when CPU load on IIS is low. Up to 10-15/hour, more when activity level is higher.
From looking at deadlock diagram in SQL Server Extended Events the deadlock is always the same:
Key Lock: object name: dbo.mdl_sessions, Index name: mdl_sess_id_pk
Key Lock: object name: dbo.mdl_sessions, Index name: mdl_sess_sid_ux
From looking at actual deadlock information in XML format this is an example of clashing queries:
SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM mdl_sessions WHERE sid = N'29ujrdb0np0vs06rrme9fnq1p1'
DELETE FROM mdl_sessions WHERE sid = N'29ujrdb0np0vs06rrme9fnq1p1'
- Can these deadlocks be fixed ?
- What are the implications of these deadlocks occurring? We have not received any error reports from users that we can contribute to these deadlocks.