Hi,

We would like to implement Retry Logic for Transient Failures in SQL Azure for certain SQL exceptions.

http://social.technet.microsoft.com/wiki/contents/articles/4235.retry-logic-for-transient-failures-in-windows-azure-sql-database.aspx

LLBLGen Pro Version: 3.5.12.317 with DataAccessAdapter + QuerySpec.

QuerySpec uses static helper methods (FetchQuery, FetchFirst, FetchSingle, FetchScalar etc), so these methods cannot override, and have to re-create similar methods with Retry logic for our needs.

Alternatively, we would like to override "some" methods in DataAccessAdapter (which executes SELECT statements), and implement retry logic with SQL Azure. I guess, it requires to override few methods in DataAccessAdapter only.

Would you please let us know DataAccessAdapter virtual methods, which executes SELECT query against the database?

Kind Regards.

What type of exception are transient for you?

According to that post, you could just write your own DataAccessAdapterExtended that inherits from DataAccessAdapter and replace some methods like: CreateNewPhysicalConnection, OpenConnection, ExecuteMultiRowRetrievalQuery, etc.

BTW, that library put extension methods on SqlConnection class. LLBLGen Runtime Framework uses DBProviderFactories, which means that it works on DBConnection object. So you will have to cast to SqlConnection (and Similar objects) in order to use that extensions, or get the library source and modify it to work on DBProviderFactory objects (DBConnection, DBCommand, etc).

Hi,

Thanks for your quick response.

Transient failures may happen during read or write database operation (not just opening a database connection) as per the following article:

http://blogs.msdn.com/b/sqlazure/archive/2010/05/11/10011247.aspx

Re-executes the failed commands or the query repeatedly, the number of times is configurable in the .config file without getting into an endless loop.

SQL Azure Errors: 40197 - The service has encountered an error processing your request. Please try again. 40501 - The service is currently busy. Retry the request after 10 seconds.

More Exceptions: http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Connection-loss_Errors

We want to implement "read" and "write" operations with Retry to avoid these transient failures. It would be nice if "DataAccessAdapter.CompatibilityLevel" has been set to "Azure" and happens magically

Read Operation: I am not sure how many DataAccessAdapter methods use read operations (all SELECT statements like fetch single, collection or scalar queries etc.)

It would be nice to inherits few virtual methods in DataAccessAdapter class, which connects to the database for reads operations, so the application can retry the query during transient failure. Would you please let me know the details?

Write Operation: Please correct me if I am wrong... I believe that the write operation can be achieved by re-submitting "UnitOfWork2.Commit()" to the database again if there is only one UnitOfWork2 in the transaction, and there won't be any duplicate records in the database. However, if there are multiple UnitOfWork2 commit to the database in the same transaction - we have to re-submit all UnitOfWork2 to the database. Is it correct?

Kind Regards.

I don't think you need retry for write operations, do you? As in the ref. link, you need retry for opening the connections and for reads, right?

On azure it's a special case, I didn't know this either, but learned about this recently. EF6 will have recovery/retry strategies and we plan to have them too in a future v4.x version. Azure apparently can drop the connection at any time and if you don't have retry strategy in place, you have to manually code recovery code.

It is planned, but it's not implemented yet.

Read Operation: I am not sure how many DataAccessAdapter methods use read operations (all SELECT statements like fetch single, collection or scalar queries etc.)

It would be nice to inherits few virtual methods in DataAccessAdapter class, which connects to the database for reads operations, so the application can retry the query during transient failure. Would you please let me know the details?

Executeretrieval methods are the ones which execute the actual query and consume the datareader.

They contain a try/finally, which cleans up the dataconnection/reader.

Write Operation: Please correct me if I am wrong... I believe that the write operation can be achieved by re-submitting "UnitOfWork2.Commit()" to the database again if there is only one UnitOfWork2 in the transaction, and there won't be any duplicate records in the database. However, if there are multiple UnitOfWork2 commit to the database in the same transaction - we have to re-submit all UnitOfWork2 to the database. Is it correct?

If Commit() fails, the whole transaction it is in rolls back, so previous commits in the same transaction which succeeded roll back too. You can use SaveTransaction to create save points between Commit() calls in the same transaction to rollback to that save point. You have to wrap the call to Commit() in a try/catch yourself, as you won't be passing true for 'autocommit' when calling Commit() as you have multiple calls to Commit() in the same transaction.

Otis wrote:

On azure it's a special case, I didn't know this either, but learned about this recently. EF6 will have recovery/retry strategies and we plan to have them too in a future v4.x version. Azure apparently can drop the connection at any time and if you don't have retry strategy in place, you have to manually code recovery code.

It is planned, but it's not implemented yet.

Read Operation: I am not sure how many DataAccessAdapter methods use read operations (all SELECT statements like fetch single, collection or scalar queries etc.)

It would be nice to inherits few virtual methods in DataAccessAdapter class, which connects to the database for reads operations, so the application can retry the query during transient failure. Would you please let me know the details?

Executeretrieval methods are the ones which execute the actual query and consume the datareader.

They contain a try/finally, which cleans up the dataconnection/reader.

Write Operation: Please correct me if I am wrong... I believe that the write operation can be achieved by re-submitting "UnitOfWork2.Commit()" to the database again if there is only one UnitOfWork2 in the transaction, and there won't be any duplicate records in the database. However, if there are multiple UnitOfWork2 commit to the database in the same transaction - we have to re-submit all UnitOfWork2 to the database. Is it correct?

If Commit() fails, the whole transaction it is in rolls back, so previous commits in the same transaction which succeeded roll back too. You can use SaveTransaction to create save points between Commit() calls in the same transaction to rollback to that save point. You have to wrap the call to Commit() in a try/catch yourself, as you won't be passing true for 'autocommit' when calling Commit() as you have multiple calls to Commit() in the same transaction.

Hello,

As you mentioned that you are planning to include Database Retry in a future LLBLGen version, I would like to ask is there any update on the progress of this feature and when could we expect something like this to be released?

Regards, Mario

It's planned for v4.1, but we don't have a release date for v4.1 yet.

Otis wrote:

It's planned for v4.1, but we don't have a release date for v4.1 yet.

Hello,

Now since you have released v4.1 can you tell me is the Database Retry implemented in that version?

Regards, Mario

Hi Mario,

Yes, it's supported on v4.1. See the documentation.

daelmo wrote:

Hi Mario,

Yes, it's supported on v4.1. See the documentation.

UPDATE: my question regarding the "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)." on SQL Azure was moved to a separate thread - http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=23158

As per the forum guidelines, please open a new thread for your question. Sorry for the trouble.