How to handle deadlock

[hstijnen]

Hi,
I'm using ZEOS7 in CBuilder XE with MySQL.
When executing a number of consecutive transactions I frequently get a deadlock message: '...raised exception class EZSQLException with message 'SQL Error: Deadlock found when trying to get lock; try restarting transaction'.

I've tried:

Code: Select all

try {
   bool suc = SelectSourceForm->Connection->ExecuteDirect(query, nrec);
}
catch (EZSQLException &exception) {
    .....
}

In case of that deadlock suc is set to false, but the EZSQLException is not catched! Is that to be expected?

What's the best way to handle this situation?

Best Regards,

Henk

[EgonHugeist]

hstijnen,

It seem's you have allways very spezial issues or faq's to post.

I'll take a look to the documentations @weekend. Actually i must admit i don't know what deadlock is..

Or can you tell me what that is exactly? Can you give us a code example that raises this exception?

Michael

[hstijnen]

Hi Michael
I only know roughly what is a deadlock. AFAIK it works as follows. When you update a row in a table, the dbms does the following:
1. put a lock on that row (to prevent simultaneous updates by different processes)
2. update the row
3. removes the lock.
When step 3 fails and another user/process tries to update that record you can get a deadlock.

However, in my case the deadlock appears when I am inserting records in some table, in which case I think nothing is to be locked. So I don't fully apprehend the phenomenon.

In the past I had some like thing. I then used ZQuery->ExecSQL, and it disappeared when using ZConnection->ExecuteDirect. Possibly ZConnection->ExecuteDirect waits for reaction from the server, while ZQuery->ExecSQL does not? But now I'm using ZConnection->ExecuteDirect...

I cannot give a simple code example, for it happens not always at the same time. Likely dependent on database traffic.

So far as I can see in the stack the EZSQLException is raised in CheckMySQLError, but the exception reaches not the calling code of my C++ program.

Here is the code:

Code: Select all

procedure CheckMySQLError(PlainDriver: IZMySQLPlainDriver;
  Handle: PZMySQLConnect; LogCategory: TZLoggingCategory; const LogMessage: string);
var
  ErrorMessage: string;
  ErrorCode: Integer;
begin
  ErrorMessage := Trim(String(StrPas(PlainDriver.GetLastError(Handle))));
  ErrorCode := PlainDriver.GetLastErrorCode(Handle);
  if (ErrorCode <> 0) and (ErrorMessage <> '') then
  begin
    if SilentMySQLError > 0 then
      raise EZMySQLSilentException.CreateFmt(SSQLError1, [ErrorMessage]);

    DriverManager.LogError(LogCategory, PlainDriver.GetProtocol, LogMessage,
      ErrorCode, ErrorMessage);
    raise EZSQLException.CreateWithCode(ErrorCode,
      Format(SSQLError1, [ErrorMessage]));
  end;
end;

called from:

function TZMySQLStatement.ExecuteUpdate(const SQL: string): Integer;
var
  QueryHandle: PZMySQLResult;
  HasResultset : Boolean;
begin
  Result := -1;
  {$IFDEF DELPHI12_UP}
  if FPlainDriver.ExecQuery(FHandle, PAnsiChar(AnsiString(SQL))) = 0 then
  {$ELSE}
  if FPlainDriver.ExecQuery(FHandle, PAnsiChar(SQL)) = 0 then
  {$ENDIF}
  begin
    DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
    HasResultSet := FPlainDriver.ResultSetExists(FHandle);
    { Process queries with result sets }
    if HasResultSet then
    begin
      QueryHandle := FPlainDriver.StoreResult(FHandle);
      if QueryHandle <> nil then
      begin
        Result := FPlainDriver.GetRowCount(QueryHandle);
        FPlainDriver.FreeResult(QueryHandle);
      end
      else
        Result := FPlainDriver.GetAffectedRows(FHandle);
    end
    { Process regular query }
    else
      Result := FPlainDriver.GetAffectedRows(FHandle);
  end
  else
    CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
  LastUpdateCount := Result;
end;

called from:

function TZAbstractConnection.ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;
var
  stmt : IZStatement;
begin
  try
    try
      CheckConnected;
      stmt := DbcConnection.CreateStatement;
      RowsAffected:= stmt.ExecuteUpdate(SQL);
      result := (RowsAffected <> -1);
    except
      RowsAffected := -1;
      result := False;
    end;
  finally
    stmt:=nil;
  end;
end;

called from:

function TZAbstractConnection.ExecuteDirect(SQL : String) : boolean;
var
  dummy : Integer;
begin
  result:= ExecuteDirect(SQL,dummy);
end;

[EgonHugeist]

Hi Henk,

Slightly i did read me in the deadlock issue. If i understand everything right than it is an issue with InnoDB and i higher transaction level than tiReadCommited.

What makes me wondering is that this error will not be raised, or do i understand you wrong? Now i see that we check for silentMySQLError and SQLError1. Hmm in my mind that could be the reason that the Exception does not reach you app... But i don't know it for sure now. I'll check, where these values are set this evening. I hope well find a solution..

Michael

[hstijnen]

Hi Michael,
I got again exceptions that I could not catch. Now i've added a line in the Zeos code:
function TZAbstractConnection.ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;
var
stmt : IZStatement;
begin
try
try
CheckConnected;
stmt := DbcConnection.CreateStatement;
RowsAffected:= stmt.ExecuteUpdate(SQL);
result := (RowsAffected <> -1);
except
RowsAffected := -1;
result := False;
raise {------ added by Henk 09-10-2012 --------}
end;
finally
stmt:=nil;
end;
end;

And now I can catch the exception!

[EgonHugeist]

hstijnen,

patch applied. Rev 1908 \testing.

Thanks Henk.