Exception thrown when running worker3 class in perf test
Bug
Normal
Open
Unresolved
Dimuthu Gamage
Chamara Silva
Thu, 2 Jul 2009 14:35:16 +0000 (UTC)
Fri, 3 Jul 2009 18:45:09 +0000 (UTC)
2.0 RC5
Registry
0
Dimuthu can you please have a look at this...
Hi,
I'm running the test case with mssql with the mentioned configuration to reproduce the issue. Apparently it will take time.
Anyway looking at the stacktrace it is difficult to derive a reason to the problem, as the database operation doing at this particular case is the first database query under that particular transaction.
Dimuthu ...you may already have read following resources.. if not .sure them help you .
Same issue with answers
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic15892.aspx
http://www.hp.isc.usouthal.edu/CIS324/sql/TransactionMgtFall2006.sql.txt
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
http://sql-server-performance.com/Community/forums/t/5899.aspx
Hi,
Thanks Indika for the links, They are really useful. I will do a comparison on facts that describes on
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx with our database.
1. properly normalized - With some exceptions (for an example path_id, reg_name can be repeating in history tables). Anyway this was done to optimized performance as path_id is mostly taken from cache..
2. accessing in same order - we have some exceptions in the basic queries as well. only the content and the resource is considered.
Put operation -> REG_CONTENT(write)-> REG_RESOURCE(write)
Get operation -> REG_RESOURCE(read) ->REG_CONTENT(read).
Since the transaction level we are using is READ_COMMITED this doesn't cause deadlocks + inconsistent data.
Put operation (for update) is a lot complex-> REG_RESOURCE(read) -> REG_CONTENT(read) -> REG_CONTENT_HISTORY(write)-> REG_RESOURCE_HISTORY(write) -> REG_CONTENT(delete)->REG_RESOURCE(delete) -> REG_CONTENT(write)-> REG_RESOURCE(write)
anyway whenever a write or delete operation is there it follow the REG_CONTENT->REG_RESOURCE order (enforced by foreign key dependency)
3. No user inputs inside the transaction.
4. No cursors are used.
5. No stored procedures (unfortunately we have to support databases without stored procedures).
6. Reduce Lock Time - Since the non-optimized custom queries slows down the transaction completion, this can be a reason to occur deadlocks.
7. We are using READ_COMMITED transaction level. (
http://www.onjava.com/pub/a/onjava/2001/05/23/j2ee.html?page=2). This causes minimum dead locks.
Dimuthu
Does REG_CONTENT and REG_RESOURCE involve two tables? .. are we using ROW Locking ?
Indika
REG_CONTENT and REG_RESOURCE are two separate tables.
Yea. we are using the default SQL Server settings. SO it is ROW locking
You can not grantee deadlock free with TRANSACTION_READ_COMMITTED. Yes most probably, it does. It only 100% guarantee - NO dirty read.
Consider a scenario. There are two transactions Ta Tb and access tables Da and Db in opposite order and operations are conflicting operations - i.e. Read-write or write-write
Ta - Da Db
Tb - Db Da
Consider
Ta has a lock on Da and tries to access (get lock) Db
Tb has a lock on Db and tries to access (get lock) Da
Simply a deadlock - one transaction will be aborted by DBMS
This kind of issue can be avoided by proper ordering of database access or proper ordering of transactions - simply needs Serially Equivalent Interleaving of operations
Serially equivalent orderings require one of the following two conditions:
Ta accesses Da before Tb, and Ta accesses Db before Tb
Tb accesses Da before Ta, and Tb accesses Db before Ta
So here, application needs to make sure Ta do work on Da and Db before, Tb begin work with them because accessing orders are reverse.
BTW, I haven't any knowledge about Registry ... so I don't know situations like above could be happened in the registry.
Even uses Row locking above can be happened if two Transaction access same Rows
I think these condition are met in the registry database,
For an example in your scenario it is,
Ta => R -> W
Tb => W-> W
But the scenarios related to REG_CONTENT and REG_RESOURCE tables are
Ta => W->W
Tb => R->R
and in theory (as i think) TRANSACTION_READ_COMMITTED state should not failed if at least one of two have R operation at the end. i.e even something like following will not fail.
Ta => W->W
Tb=> W->R
BTW I think the reason behind the issue in this particular issue is not the REG_CONTENT and REG_RESOURCE tables. It is caused by custom query that has the following where clause.
"SELECT RT.REG_RATING_ID FROM REG_RESOURCE_RATING RT, REG_RESOURCE R " +
"WHERE (R.REG_VERSION=RT.REG_VERSION OR " +
"(R.REG_PATH_ID=RT.REG_PATH_ID AND R.REG_NAME=RT.REG_RESOURCE_NAME)) " +
"AND R.REG_DESCRIPTION LIKE ?";
Mistakenly i put wrong Users number actual tested no is 40.
Looking at the jtds code, i found the error message printed here is come from the server. So changing the driver will not solve the issue.
The order for put(update) operation should be corrected to
REG_RESOURCE(read) -> REG_CONTENT(read) -> REG_CONTENT_HISTORY(write)-> REG_RESOURCE_HISTORY(write) -> REG_RESOURCE(delete)->REG_CONTENT(delete) -> REG_CONTENT(write)-> REG_RESOURCE(write)
downgrading the issue as it is not reproducible + fix for this is not releasing to the upcoming release
Estimated Complexity
Moderate
Severity
Major
Date of First Response
Thu, 2 Jul 2009 15:21:13 +0000 (UTC)
THE DEVELOPER PORTAL FOR SOA
