SP ODBC RetryOnError

[Paul Henson]

I've got a deployment that is storing session state in a Microsoft SQL 
Server instance via ODBC and they are seeing session update failures due 
to deadlocks:

ODBC Error: 40001:1:1205:[Microsoft][ODBC Driver 17 for SQL Server][SQL
Server]Transaction (Process ID 79) was deadlocked on lock resources with 
another process and has been chosen as the deadlock victim. Rerun the 
transaction.
ODBC Error: 01000:2:3621:[Microsoft][ODBC Driver 17 for SQL Server][SQL
Server]The statement has been terminated. 

error storing back mapping of NameID for logout: ODBC StorageService
failed to update record.

My initial thought was to add the RetryOnError option so deadlock 
failures (error #1205) would be retried. However, upon initial review of 
the retry code in odbc-store/odbc-store.cpp, if there are multiple 
errors returned from a given call, the call is only retried if the last 
error seen is on the retry list, which in this case would be 3621, not 
1205. 3621 is a much more generic error that is returned for different 
underlying failures then the deadlock, potentially including ones that 
should not be retried.

As I misunderstanding this section of code?

     pair<bool,bool> res = make_pair(false,false);
     do {
         ret = SQLGetDiagRec(htype, handle, ++i, state, &native, text, 
sizeof(text), &len);
         if (SQL_SUCCEEDED(ret)) {
             m_log.error("ODBC Error: %s:%ld:%ld:%s", state, i, native, 
text);
             for (vector<SQLINTEGER>::const_iterator n = 
m_retries.begin(); !res.first && n != m_retries.end(); ++n)
                 res.first = (*n == native);
             if (checkfor && !strcmp(checkfor, (const char*)state))
                 res.second = true;
         }
     } while(SQL_SUCCEEDED(ret));
     return res;

res.first will be set to true the first time through the loop for error 
1205, but then set back to false the second time through the loop when 
error 3621 is seen, which will then be returned, and the statement will 
not be retried if only 1205 is in the list.

Thanks…

-- 
Signet - The Art of Access
https://www.signet.id/