I have a C# application which is used simultaneously by around 10 users. Each user updates a particular row in a table through the application each second (sometimes taking under a second too). So there are around 10-20 calls to an UPDATE statement each second.

Performance is generally ok, and the update statement takes around 50 – 100 milliseconds to execute. However, every few seconds, there is a huge delay, there the execution of the update statement takes around 10 seconds to process on one or more user machines. It then goes back to normal, and repeates this process every now and then.

The table has a few million records in it, however the UPDATE takes place based on a particular id which happens to be the primary key, so I would assume that there is no problem in this statement. The statement being executed is shown below:

UPDATE pc  SET    corrected_plate = @correctedPlate,         corrected_fk_username = @username,         corrected_timestamp = NOW(),         corrected_quality_assessment = @qualityAssessment,         unable_to_correct = @unableToCorrect,         corrected_plate_in_whitelist = @correctedPlateInWhiteList  WHERE  id = @id; 

I am calling the statement like so:

 connectionString += ";Allow User Variables=True;";          using (MySqlConnection connection = new MySqlConnection(connectionString))         {             connection.Open();              using (MySqlCommand cmd = connection.CreateCommand())             {                 cmd.CommandTimeout = 0;                  if (listParameters != null && listParameters.Count > 0)                 {                     foreach (string currentKey in listParameters.Keys)                     {                         cmd.Parameters.Add(new MySqlParameter(currentKey, GetDictionaryValue(listParameters, currentKey)));                     }                 }                  cmd.CommandText = sql;                 cmd.ExecuteNonQuery();                  return true;             }         } 

Table Schema

CREATE TABLE `pc` (   `id` bigint(20) NOT NULL DEFAULT '0',   `plate` varchar(10) NOT NULL DEFAULT '',   `seenDate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',   `insertedDate` datetime DEFAULT NULL,   `comment` text,   `imageSlices` smallint(11) NOT NULL DEFAULT '0',   `camerauid` int(10) NOT NULL DEFAULT '0',   `gpsuid` int(10) unsigned DEFAULT NULL,   `confidence` int(10) NOT NULL DEFAULT '0',   `last_accessed_timestamp` timestamp NULL DEFAULT NULL,   `corrected_plate` varchar(45) DEFAULT NULL,   `corrected_fk_username` varchar(45) DEFAULT NULL,   `corrected_timestamp` timestamp NULL DEFAULT NULL,   `corrected_quality_assessment` varchar(200) DEFAULT NULL,   `unable_to_correct` tinyint(1) NOT NULL DEFAULT '0',   `corrected_plate_in_whitelist` tinyint(1) DEFAULT '0',   `last_accessed_fk_username` varchar(45) DEFAULT NULL,   `original_camera_id` int(10) DEFAULT NULL,   `fk_zone_for_correction` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `corrected_fk_username_idx` (`corrected_fk_username`),   KEY `corrected_plate` (`corrected_plate`),   KEY `last_accessed_fk_username_idx` (`last_accessed_fk_username`),   KEY `idx_seenDate` (`seenDate`),   KEY `corrected_timestamp_idx` (`corrected_timestamp`),   KEY `fk_original_camera_id_idx` (`original_camera_id`),   KEY `fk_camera_idx` (`camerauid`),   KEY `idx_corrected_plate_seenDate` (`corrected_plate`,`seenDate`),   KEY `zone_for_correction_fk_idx` (`fk_zone_for_correction`),   KEY `idx_corrected_plate_zone` (`fk_zone_for_correction`,`corrected_plate`),   CONSTRAINT `corrected_fk_username` FOREIGN KEY (`corrected_fk_username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT `fk_camera` FOREIGN KEY (`camerauid`) REFERENCES `cameras` (`camera_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT `last_accessed_fk_username` FOREIGN KEY (`last_accessed_fk_username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT `zone_for_correction_fk` FOREIGN KEY (`fk_zone_for_correction`) REFERENCES `zones` (`zone_number`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Any ideas what can be done to solve this problem? Keeping in mind that the statement takes a few milliseconds to process normally, but every few seconds experiences a big spike which takes around 10 seconds to process, essentially blocking the respective user.