Search Posts

Preventing deadlock

I have the following transaction code:

using(MySqlConnection connection = new MySqlConnection(connectionString)) {
 connection.Open();

 using(MySqlTransaction transaction = connection.BeginTransaction()) {
  using(MySqlCommand cmd = new MySqlCommand()) {
   cmd.CommandTimeout = 0;

  ...
  ...

   cmd.Connection = connection;
   cmd.Transaction = transaction;
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.CommandText = sp;
   object retVal = cmd.ExecuteScalar();
   transaction.Commit();

   return retVal;
  }
 }
}

It is running a stored procedure, whose code is shown below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_load`(IN param_first_sighting BIGINT(20), IN param_interval INT(11))
BEGIN
DECLARE out_param INT DEFAULT 0;
SET out_param = (SELECT sightinguid FROM pc
WHERE
        (last_accessed_timestamp IS NULL
        OR last_accessed_timestamp < (NOW() - INTERVAL param_interval MINUTE))
        AND sightinguid > param_first_sighting LIMIT 1); 
        UPDATE pc SET last_accessed_timestamp = NOW() WHERE sightinguid = out_param;
        SELECT out_param;  
END

Basically what this SP is trying to accomplish is the following:

  1. Gets the next sightinguid to be processed (either the one which was last accessed over 30 minutes ago, otherwise the next one in line).
  2. Updates that row with the last_accessed_timestamp being the current time, to lock it from being accessed by other users
  3. Returns the sightinguid obtained for further processing, while the row is “locked” based on the timestamp for 30 minutes

The above is prone to deadlock, infact I am getting a deadlock error every few minutes, since around 10 users are working at once. How can the above code be optimised and improved to prevent deadlock, but also remain fast?

Source: Stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *