SQLServerWiki

“The Only Thing That Is Constant Is Change”

Have you breached Microsoft supportability guidelines? check this SQL Server 2008R2 and Commerce Server 2002 compatibility issue.

Posted by database-wiki on July 9, 2012

Analysis: The blocking which occurred on 2012-05-02 was caused by an application running on Internet Information Services which began an serialized transaction, then executed a FETCH API_CURSOR.  The spid 80 is blocking spid 443 which is trying to do an UPDATE.

2012-05-02 14:01:14.69 spid25s     deadlock-list

2012-05-02 14:01:14.69 spid25s      deadlock victim=process37b3b0088

2012-05-02 14:01:14.69 spid25s       process-list

2012-05-02 14:01:14.69 spid25s        process id=process37b3b0088 taskpriority=0 logused=0 waitresource=KEY: 25:282888923447296 (00d787d3a92d) waittime=4062 ownerId=657923656 transactionguid=0xeb71f1d37fd3b64ba3ada0544c07896c transactionname=DTCXact lasttranstarted=2012-05-02T14:01:10.570 XDES=0x2a9a66b50 lockMode=X schedulerid=6 kpid=4888 status=suspended spid=443 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-05-02T14:01:10.640 lastbatchcompleted=2012-05-02T14:01:10.640 clientapp=Internet Information Services hostname=BARTHCC16 hostpid=15568 loginname=itradenet isolationlevel=serializable (4) xactid=657923656 currentdb=25 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

2012-05-02 14:01:14.69 spid25s         executionStack

2012-05-02 14:01:14.69 spid25s          frame procname=adhoc line=1 stmtstart=36 sqlhandle=0x020000002d8ae4026401acb1445ca9cb35780c2e29e56507

2012-05-02 14:01:14.69 spid25s     UPDATE [counters] SET [next]=@Param000004

2012-05-02 14:01:14.69 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2012-05-02 14:01:14.69 spid25s     unknown

2012-05-02 14:01:14.69 spid25s         inputbuf

2012-05-02 14:01:14.69 spid25s     (@Param000004 int)UPDATE [counters] SET [next]=@Param000004

2012-05-02 14:01:14.69 spid25s        process id=processaecf948 taskpriority=0 logused=10000 waitresource=KEY: 25:282888923447296 (00d787d3a92d) waittime=4063 schedulerid=2 kpid=816 status=suspended spid=80 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2012-05-02T14:01:10.630 lastbatchcompleted=2012-05-02T14:01:10.630 clientapp=Internet Information Services hostname=BARTHCC17 hostpid=16284 loginname=itradenet isolationlevel=serializable (4) xactid=657923735 currentdb=25 lockTimeout=4294967295 clientoption1=671156256 clientoption2=128056

2012-05-02 14:01:14.69 spid25s         executionStack

2012-05-02 14:01:14.69 spid25s          frame procname=adhoc line=1 sqlhandle=0x020000005798e509e94e855475a21ebd39f0ab26298cdc0d

2012-05-02 14:01:14.69 spid25s     FETCH API_CURSOR00000000000CEC06

2012-05-02 14:01:14.69 spid25s          frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000

2012-05-02 14:01:14.69 spid25s     unknown

2012-05-02 14:01:14.69 spid25s         inputbuf

2012-05-02 14:01:14.69 spid25s     FETCH API_CURSOR00000000000CEC06

2012-05-02 14:01:14.69 spid25s       resource-list

2012-05-02 14:01:14.70 spid25s        keylock hobtid=282888923447296 dbid=25 objectname=Live_Transactions.dbo.counters indexname=PK_counters id=lock2aa662900 mode=U associatedObjectId=282888923447296

2012-05-02 14:01:14.70 spid25s         owner-list

2012-05-02 14:01:14.70 spid25s          owner id=processaecf948 mode=S

2012-05-02 14:01:14.70 spid25s         waiter-list

2012-05-02 14:01:14.70 spid25s          waiter id=process37b3b0088 mode=X requestType=convert

2012-05-02 14:01:14.70 spid25s        keylock hobtid=282888923447296 dbid=25 objectname=Live_Transactions.dbo.counters indexname=PK_counters id=lock2aa662900 mode=U associatedObjectId=282888923447296

2012-05-02 14:01:14.70 spid25s         owner-list

2012-05-02 14:01:14.70 spid25s          owner id=process37b3b0088 mode=U

2012-05-02 14:01:14.70 spid25s          owner id=process37b3b0088 mode=U

2012-05-02 14:01:14.70 spid25s         waiter-list

2012-05-02 14:01:14.70 spid25s          waiter id=processaecf948 mode=U requestType=wait

Behavior has changed for Dynamic API Cursor from SQL 2000 to SQL 2005 and so forth:

    -> In SQL 2000, when fetching a row from the cursor, it will acquire U lock
    -> In SQL 2005 and so forth, when fetching a row from the cursor, it will acquire S lock first and then convert the S lock to U lock.

We have the following answers from you:

1. What was you version of SQL Server for the commerce server database before SQL Server 2008 R2?

Yes database was upgraded from SQL Server 2000 and bypassed SQL2005 on the upgrade to 2008 R2.

2. Can you confirm if the above objects involved in the deadlock belong to commerce server database with dbid=25? If yes we may have to contact commerce server team before suggesting any changes from the SQL Server side.

Yes and the database dbid=25 is database ‘Live_Transactions’.

3. Is it possible for you provide us the PSSDIAG for further analysis, how often does this deadlock occur?

Checking with the client on this. ( won’t be of much help considering the deadlock and SQL Server design change from SQL Server 2000 to SQL Server 2005 and hence forth.)

Ask for commerce server team!

We have a concern that why isolationlevel=serializable (4)? Can this be changed? since it’s a commerce server schema we are not supposed to take this call. Do you think that’s the reason commerce server is not support of SQL Server 2008 R2 also considering the fact “Behavior has changed for Dynamic API Cursor from SQL 2000 to SQL 2005 and so forth” from the SQL Server side?

For your understanding,

Serializable is the highest isolation level.

serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon.

For example, ( This is exactly what’s happening in our scenario. )

— USER B

USE AdventureWorks

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT COUNT(*) FROM Purchasing.Vendor WHERE CreditRating = 2

— returns a count of 9

— USER A

USE AdventureWorks

GO

BEGIN TRAN

INSERT INTO Purchasing.Vendor

(AccountNumber, Name, CreditRating, PreferredVendorStatus,

ActiveFlag, ModifiedDate)

VALUES ( ‘000000A’,’My Test Vendor’,2,1,1, GETDATE())

— DEADLOCK, BECAUSE THIS INSERT WOULD AFFECT THE ROW COUNT

— result for the serializable transaction for USER B

— USER B

COMMIT TRAN

— USER A

— INSERT finally goes through, can commit

COMMIT TRAN

— USER B

SELECT COUNT(*) FROM Purchasing.Vendor WHERE CreditRating = 2

— NOW returns a count of 10

 

After collaboration with the commerce server escalation services,

We don’t think there is scope for any changes to Commerce Server to stop this issue from happening.

We might be able to change the Serialization Level on the COM+ component Microsoft.CommerceServer.Internal.Orders.MtsTxHelper as seen in this screen shot.

But we have no idea what impact this might have on other behaviour as discussed below.

why that UPDATE Counters statement is being executed?

Commerce Server handles Orders and the final checkout component converts a user’s basket to an Order.
As part of this process we get an OrderNumber and then update the counters table so that the next order will get a higher number.

This process is carried out as a transaction from some Com+ Components that carry out this process.

So we don’t set the isolation level specifically in Commerce Server but rather carry out this as part of the overall Com+ Transaction.

295570  INFO: Transactions and Isolation Levels in COM+

http://support.microsoft.com/kb/295570/EN-US

So unfortunately there is nothing we can do to change the product as it’s no longer supported as they have pointed out.

916757  Commerce Server 2002 does not function together with Visual Studio 2005 and SQL Server 2005

http://support.microsoft.com/kb/916757/EN-US

http://blogs.msdn.com/b/rdonovan/archive/2006/02/07/526674.aspx

The reason that Commerce Server 2002 is not supported with SQL 2005 and above is because the two products were never tested together.
The lock issue is one of the things that the product group had to work around and change the behaviour in Commerce Server 2007 and above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: