杀虫记录(4)-第一次遭遇性能问题

        做了很久的功能测试,按照别人写的性能测试步骤，我也做了不少性能测试。但是一直都是按部就班没有发现啥问题，终于又一次发现了一次性能问题--DB2 911死锁。

        

        产品D是有2套用户界面的，除了常用的图形界面还对外发布了一套API，开发人员为产品D封装了CRUD(增删改查)和工作流的API。我就拿开发写好的脚本来按部就班的跑Case。

       每次都是跑5个Create，5个Retrieve,5个Update和5个Delete分别在在不同的项目上。偶然的一次，我把Update和Delete都做在同一个项目A上面，然后发现界面上面的操作很慢,有时候直接抛错911- DB2 Deadlock。

       于是我没有放过这个小错，开个开发一个Performance的defect。

       

---

We run 2 D Client API threads, one Item delete thread, the other one is Folder operation thread, including 

Folder create/delete, add/remove/retrieve Items to/from folder. The Item type used in the Folder operation thread 

is the same one as delete thread. So there may exist conflict operation on the same Item type.

 

After about 30 mins running, -2147216371 error happened, then it is found creating a folder 

through either API or desktop takes more than 30 seconds.

Through logs, it looks like there is deadlock and The D default value for 

LOCKTIMEOUT is 30 seconds. That maybe why folder creation need more than 30 secs to complete. 

Below are the detailed error log 

客户端错误：

[EXC]: 08/29/2007 07:54:53.890 @ server13 (127.0.0.1);  # DTraceLog

[USR]: D_User (C:\Documents and Settings\Default User) @ C:\Program Files\18m\D

[THD]:      bd8

[PRS]:      e80

[MSG]: Exception: D5133A: Adding or deleting links failed.  \ server detected an unexpected SQL error.;  S7015: During an SQL operation in the server, an unexpected error occurred.  For details on the error, refer to the database document.  (STATE) : [S RC = 7015, SQL RC = -911]

[08/29/2007 07:54:53.890]      bd8 X: gpsobjs_HandleException - exiting

[08/29/2007 07:54:53.890]      bd8 X: gpsobjs_FolderMemberObjDelete - exiting

服务器端错误：

[08/29/2007 08:55:30.156]     1578 X: Tester001-29708072593004071177-31990103843004071177-- AbstractCRUD::getErrorMessageId(int returnCode='7015')  return(errorId='3616')

[08/29/2007 08:55:30.156]     1578 E: Tester001-29708072593004071177-31990103843004071177-  PRetrieve::retrieveBatch(): (!) ERROR (!): While invoking the GETS SP or extracting & populating results, encountered Exception "D3616A: An unexpected SQL error occurred;  7015: During an SQL operation in the library server, an unexpected error occurred.  For detai on the error, refer to the database document.  (STATE) : [ RC = 7015, SQL RC = -911]".  Rolling Back Transaction and Rethrowing.

[08/29/2007 08:55:30.156]     1578 T: Tester001-29708072593004071177-31990103843004071177-+ Datastore::getTxManager()

---

开发按照以下的方法来要求我提供数据：

1 把DB2 默认的Counter全部置为0，然后重现这个错误，取到每隔10秒的Snapshot。

然后提出了个折中的Workround:

在Server端设置上如下的DB2Set 参数

The db2set keywords include:

DB2_EVALUNCOMMITTED=ON Defers row locking during a scan until after the evaluation of the uncommitted row. As a result, the scan skips rows that do not satisfy the WHERE predicate. This minimizes lock time because DB2 Content Manager users typically only search and retrieve on rows that are not being created or modified. DB2_SKIPINSERTED=ON Skips the check for uncommitted inserts during a scan. This reduces lock wait time whenever many items are created. DB2_SKIPDELETED=ON Skips uncommitted inserts during a scan. This reduces lock wait whenever many items are deleted.
我设置上后发现没有问题再发生了。不管如何，都不会出现911错误了。
然后在产品上加上TechNote，如果客户遇到就让他们按照这种来设置以避免死锁。
我总在想，如果奥运订票网站，如果火车订票网站也能有这样全面的测试，也能有当时还是实习研究生的我们来测试，或许不会有那么糟糕的网站。
不过，中国也是也是中国任何做项目的最大的事业环境因素,不是吗？从那以后，我发现在测试这个行当，如果你只是墨守成规，是发现不了任何问题的。并且促进了我对DB2和Oracle的学习。这是后话。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9562545/viewspace-748713/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9562545/viewspace-748713/