AbleCommerce, CommerceBuilder, database transaction safety

[bataras]

I'm trying to figure out how to make a series of CommerceBuilder API calls happen within a DB transaction.

We have working code that using CB to import orders from a 3rd party. It uses pure CommerceBuilder APIs. It doesn't make any proprietary DB connections. Here are a few sparse pieces of the import code just to give a flavor of the call patterns:

Code: Select all

        CheckoutRequest checkoutRequest = new CheckoutRequest(null);
        CheckoutResponse checkoutResponse = basket.Checkout(checkoutRequest);
        Order order = OrderDataSource.Load(checkoutResponse.OrderId);

....

                User user = UserDataSource.CreateUserInstance();
                user.Save();
                user.Basket.Save();
                basket = user.Basket;
                basket.Clear();

                Address billAddress = user.PrimaryAddress;

                //parse name
                ParsedName BillToName = new ParsedName(fields[5]);
                billAddress.FirstName = BillToName.FirstName;
                billAddress.LastName = BillToName.LastName;

                billAddress.Email = fields[4];
                billAddress.Phone = fields[6];
                billAddress.Save();

                Product p = ProductDataSource.LoadForCriteria(string.Format("Sku = '{0}'", sku))[0];
                BasketItem item = BasketItemDataSource.CreateForProduct(p.ProductId, qty);
                item.BasketId = basket.BasketId;
                basket.Items.Add(item);

The import process works fine. BUT if I attempt to wrap the otherwise working code in a transaction as follows:

Code: Select all

Token.Instance.Database.BeginTransaction(IsolationLevel.Serializable);
DoImportWork();
Token.Instance.Database.CommitTransaction();

Somewhere during the import where all the CommerceBuilder calls are being made, I get a transaction deadlock exception:

Code: Select all

[SqlException (0x80131904): Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   CommerceBuilder.Data.Database.ExecuteNonQuery(DbCommand command) +233
   CommerceBuilder.Orders.Basket.a(Basket A_0, Boolean A_1) +632
   CommerceBuilder.Orders.Basket.Clear() +10

This to me indicates that when using the CommerceBuilder APIs, they don't always use the same DB connection that is exposed via Token.Instance.Database. ie, sometimes they hit the DB in a different connection.

Can someone please help me understand how the DB connections and transactions are setup? We -have- to have reliable transactionality if we're going to customize and grow our website using the CommerceBuilder APIs. It makes me wonder if the transactionality is truly safe in the rest of the off the shelf AbleCommerce code too (ie the Admin web pages).

thanks

[mazhar]

AbleCommerce is not making use of any other connection in back end. Make sure that you are using proper isolation level for transaction. It may be due to some phantom read in your import routine. Try

Code: Select all

database.BeginTransaction(IsolationLevel.RepeatableRead);

[bataras]

If you check the 2nd code snip I pasted above, notice I am wrapping in the CommerceBuilder calls in a transaction that is isolation level serializable.

If CB is really using a single connection, then my next guess would be that the connection from a previous HTTP call has perhaps not been closed (or if CB was using a transaction in that connection, the tx wasn't commited) and is thus has left one or more tables write protected via a select. Maybe it's an autocommit issue?

I assume CommerceBuilder.Services.AbleCommerceHttpModule is grabbing db a db connection from a pool for use throughout an http request via Token.Instance.Database...

In may example above, the code that is executing is from a custom admin page that has been arrived at by navigating through admin -> Manage -> Import/Export Affiliates.

I haven't captured the deadlock in sql server's profiler yet. But there are indeed 4 or 5 connections to the DB by the time I navigate to that page. They hand around for a minute or more and then go away. I will re-try my test, but wait for those DB connections to go away.

Is there any way to control the connection pooling, autocommit, etc that AbleCommerceHttpModule is doing?

thanks

[bataras]

I've retested the transactional code, waiting until the latent connections to the DB are closed by AC/CB. The transaction succeeds. So it appears that in navigating to the affiliate import page via Login -> Admin -> Manage, the cached DB connections that AbleCommerce creates are holding a read lock (probably from a simple "select ac_user" that are preventing a transaction at isolationlevel serializable from running from a subsequent web request.

[Logan Rhodehamel]

Question... is the checkout procedure included inside your transaction?

[bataras]

Yeah I'm trying to do everything inside a transaction that is isolation level serializable. It -feels- like something simple related to the other DB connections that are opened and hang around for a few minutes as you browse through the Admin pages.

[bataras]

On a related note, I can't seem to begin nested transactions. That is supported by sql server and the ms enterprise library. That is something that we may really want to do as we develop our own business methods that call CommerceBuilder. A given business method (A) may want to provide an autonomous service that is callable externally or that may be called in the context of another business method (B) that has already begun a transaction.

I suppose it's possible to detect on entry to (A) whether a transaction already exists and if not to open its own transaction. But it's cleaner to make the database work for us and just always begin a transaction rather than work around limitations in the code.

[Logan Rhodehamel]

I believe I spotted a flaw in our code for nesting transactions. It's not a feature we use internally so it was never tested. What confuses me is you say you can get the transaction to work if you wait?

And if you do not include the call to checkout within your transaction, then are you able to make it work?

[bataras]

cool! I don't have time today to try the non-checkout test. I'll try to get to it maybe Monday.

wrt getting it to work by waiting, here's what I mean:

The transactional operation is triggered by navigating to a web page (CustomImport) under /Admin and clicking the Import button. When that button is clicked, the transaction is opened, a bunch of CommerceBuilder APIs are called (as per code clips above), an the transaction is committed.

In the process of navigating to CustomImport, 2 or 3 other standard pages are clicked through under /Admin. If I watch the DB connections in SQL Server using the activity viewer tool, I see connections being opened to the DB as pages under /Admin are browsed. This is to be expected.

If I click through to CustomImport, but do -not- click the Import button, and just wait, I can see those 2 or 3 DB connections to SQL Server stay connected. But after a minute or two, they get closed. That is also reasonable behavior. It appears you are using a DB connection pool of some sort to cache DB connections.

If I click the Import button (to begin the transaction) BEFORE AC"s cached DB connections get closed, I get the deadlock failure.

If I navigate to the CustomImport page, but WAIT for the cached DB connections to close out and then click the Import button, the whole transaction succeeds.

This tells me that the selects and/or updates that AC is doing in the DB while rendering normal /Admin web pages are probably obtaining and holding locks in an order that is the reverse of some lock sequence the CB APIs are using within my import transaction.

While it is reasonable (and expected) that AC will want to cache DB connections as individual web pages are rendered (I assume the AbleCommerceHttpModule object is doing that), I would expect any resources (eg locks) that are obtained in the DB during that to be release once the web page is rendered and AbleCommerceHttpModule sees the request completion.

Of course that doesn't cover the case where 2 users are using the Admin pages and one is trying run the import transaction. But we need it to work for at least one user.

An easy way to solve this would be if there is a way to configure AbleCommerceHttpModule to immediately close whatever connection it has opened while rendering a webpage once the request is complete. At least for pages under the Admin tree.

[Logan Rhodehamel]

An easy way to solve this would be if there is a way to configure AbleCommerceHttpModule to immediately close whatever connection it has opened while rendering a webpage once the request is complete. At least for pages under the Admin tree.

But it does. That's the part I am trying to understand. We use connections from the pool and they are released back to the pool immediately after use. This is handled by Microsoft Enterprise Library.

Let me know what happens if you move the checkout call outside of your transaction. It might be a clue.

[bataras]

I'll let you know about moving the checkout.

What I meant about releasing the connection, is is there some configuration setting for the connection pool whereby we can say "when a connection that happens under /admin, -close- the connection when it is returned to the pool". If the connects get closed (not just returned to the pool), that solves it, although it's hacky.

Another thing to try is when a connection is retrieved from the pool, always begin a transaction. Then always commit the transaction when returning the connection to the pool. Or if a top level exception is caught, rollback the transaction, then return to the pool.

Another thing to try instead of transactions is enabling auto-commit when connections are created. Not sure if that will work, but it might.

This is all kind of bizarre though. If what you say is true that connections are returned to the pool when a web page finishes rendering, then those connections should not be able to participate in a deadlock because no thread in the code should be trying to do something in the DB with them at that point; unless the DB pool itself is somehow trying to tickle the connections periodically.

I can capture the deadlock in the sql profiler to see what 2 thread are contending for what 4 DB objects, but just don't have the time today