Bug 62910 - tomcat-jdbc global pool transaction problem
Summary: tomcat-jdbc global pool transaction problem
Status: NEEDINFO
Alias: None
Product: Tomcat Modules
Classification: Unclassified
Component: jdbc-pool (show other bugs)
Version: unspecified
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: Tomcat Developers Mailing List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-11-15 05:56 UTC by zhengfc
Modified: 2018-11-16 16:59 UTC (History)
0 users



Attachments
transaction (44.00 KB, application/vnd.ms-excel)
2018-11-15 05:56 UTC, zhengfc
Details

Note You need to log in before you can comment on or make changes to this bug.
Description zhengfc 2018-11-15 05:56:33 UTC
Created attachment 36261 [details]
transaction

I use tomcat-jdbc as connnection-pool, when I deploy multi web apps use the global connection, will cause merge transaction problem.
So when app A insert a record to a table, at same time app B update a record the oracle transaction is:
------------------------------------------------------------------
set transaction read write;
insert into "GAME"."GAME_DELIVER_RECORD"("ID","GAME_ORDER_SEQ","GAME_ORDER_TIME","SKU_STOCKID","PACKAGE_SEQ","SUPPLIER_CODE","SUPPLIER_SEQ","SUPPLIER_MDSECODE","SUPPLIER_NUM","D_STATUS","SUPPLIER_INPRICE","DELIVERED_TIME","DELIVER_TIME","REMARK","CREATE_TIME","UP_TIME","BUY_INFO","LOGISTICS_SEQ","LOGISTICS_TIME","MERCH_ACCOUNT","SUPPLIER_TIME","PRIORITY_STATUS","NOTICE_COUNT","SUPPLIER_PAV") values ('118757','364045','20181114175937','150061509296','G364045-20181114175937Z0033','Z0033',NULL,NULL,NULL,'NA',NULL,NULL,NULL,NULL,TO_DATE('14-11月-18', 'DD-MON-RR'),TO_DATE('14-11月-18', 'DD-MON-RR'),'{"businessChannelCode":"SPDB","buyNum":1,"gameAccount":"139123457","groupId":"","Ip":"10.248.192.108","mobile":"13255556667","serverName":"","sellPrice":29.7,"appCode":"QB","serverId":"","groupName":"","skuStockId":"150061509296","gameCode":"20150701a7ded3ccbc4b44b58cc5c1f9bc2210f0","netWarAccount":"","channelCode":"SPDB.CW.QB"}',NULL,'20181114175937',NULL,NULL,'2','0','1');
update "GAME"."GAME_TRANSACTION_DETAIL" set "SUPPLIER_CODE" = 'Z0033', "SUPPLIER_PRICE" = '940000' where "ID" = '334872' and "GAME_TRANS_DATE" = '20181114175937' and "GAME_ACCOUNT" = '139123457' and "NETWAR_ACCOUNT" IS NULL and "GAME_ROLE" IS NULL and "GROUP_ID" IS NULL and "SERVER_ID" IS NULL and "CAMP" IS NULL and "GROUP_NAME" IS NULL and "SERVER_NAME" IS NULL and "GAME_CODE" = '20150701a7ded3ccbc4b44b58cc5c1f9bc2210f0' and "GAME_NAME" = 'Q币' and "BUY_NUM" = '1' and "SUPPLIER_CODE" = 'Z0067' and "SUPPLIER_PRICE" = '29700000' and "SELL_PRICE" = '29700000' and "PROMOTION_TYPE" IS NULL and "PROMOTION_TOTAL_AMOUNT" = '0' and "USER_ID" = '71100221' and "CAMPNAME" IS NULL and "SKUSTOCKID" = '150061509296' and "SKUNAME" = '30 Q币' and "PHONENUM" = '13255556667' and "GAMELEVEL" IS NULL and "CUSTOMERTELNO" IS NULL and "BACKENDNAME" = '模拟支付' and "GAME_TRANS_SEQ" = '364045' and "JOB_CODE" IS NULL and "JOB_NAME" IS NULL and "GAME_PWD" IS NULL and "QQNUMBER" IS NULL and "DELIVER_TIME" IS NULL and "OPERATOR_ID" IS NULL and "OPERATOR_NAME" IS NULL and "GAME_CIPHER" IS NULL and "RESIDUE_MONEY" IS NULL and "MDSEDESC" IS NULL and "GAME_EXTENSION" IS NULL and "REASON_TYPE" IS NULL and "REASON_DETAIL" IS NULL and "CHANNEL_ORDER_SEQ" IS NULL and ROWID = 'AABZBbAAEAAA3LlAAi';
update "GAME"."GAME_DELIVER_RECORD" set "SUPPLIER_MDSECODE" = '1', "SUPPLIER_NUM" = '30', "D_STATUS" = '02', "SUPPLIER_INPRICE" = '940000', "DELIVER_TIME" = '20181114175942', "MERCH_ACCOUNT" = 'testZ0033test' where "ID" = '118757' and "GAME_ORDER_SEQ" = '364045' and "GAME_ORDER_TIME" = '20181114175937' and "SKU_STOCKID" = '150061509296' and "PACKAGE_SEQ" = 'G364045-20181114175937Z0033' and "SUPPLIER_CODE" = 'Z0033' and "SUPPLIER_SEQ" IS NULL and "SUPPLIER_MDSECODE" IS NULL and "SUPPLIER_NUM" IS NULL and "D_STATUS" = 'NA' and "SUPPLIER_INPRICE" IS NULL and "DELIVERED_TIME" IS NULL and "DELIVER_TIME" IS NULL and "REMARK" IS NULL and "CREATE_TIME" = TO_DATE('14-11月-18', 'DD-MON-RR') and "UP_TIME" = TO_DATE('14-11月-18', 'DD-MON-RR') and "BUY_INFO" = '{"businessChannelCode":"SPDB","buyNum":1,"gameAccount":"139123457","groupId":"","Ip":"10.248.192.108","mobile":"13255556667","serverName":"","sellPrice":29.7,"appCode":"QB","serverId":"","groupName":"","skuStockId":"150061509296","gameCode":"20150701a7ded3ccbc4b44b58cc5c1f9bc2210f0","netWarAccount":"","channelCode":"SPDB.CW.QB"}' and "LOGISTICS_SEQ" IS NULL and "LOGISTICS_TIME" = '20181114175937' and "MERCH_ACCOUNT" IS NULL and "SUPPLIER_TIME" IS NULL and "PRIORITY_STATUS" = '2' and "NOTICE_COUNT" = '0' and "SUPPLIER_PAV" = '1' and ROWID = 'AABY/cAAEAAA4GRAAD';
commit;
---------------------------------------------------------------------
I have two problem:
  why two apps can got same connection?
  why two apps operate merge one transaction?
I deploy the apps on jboss not get the problem.

Thanks for any reply
Comment 1 zhengfc 2018-11-15 08:25:12 UTC
I change to c3p0 the apps is okay, I compare the tomcat-jdbc and c3p0 code, I found is cause by getConnnction(), c3p0 is sycn and tomcat-jdbc is unsycn.
Comment 2 Christopher Schultz 2018-11-15 20:06:54 UTC
Please tell us how you are configuring your connection pool and also how you are using it.

There are plenty of ways to misuse a connection pool that do not mean the pool itself has bugs.
Comment 3 zhengfc 2018-11-16 01:21:39 UTC
The context.xml and server.xml, I deploy multi app use the game connection will cause transaction problem.
-------------------------context.xml-----------------------------
<ResourceLink name="game" global="game" auth="Container" type="javax.sql.DataSource" />

-------------------------server.xml------------------------------
<GlobalNamingResources>
  <!-- Editable user database that can also be used by
       UserDatabaseRealm to authenticate users
  -->
  <Resource name="UserDatabase" auth="Container"
            type="org.apache.catalina.UserDatabase"
            description="User database that can be updated and saved"
            factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
            pathname="conf/tomcat-users.xml" />
<Resource global="game"
          name="game"
          auth="Container"
          type="javax.sql.DataSource"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          validationQuery="SELECT 1 from dual"
          timeBetweenEvictionRunsMillis="30000"
          minEvictableIdleTimeMillis="600000"
	  numTestsPerEvictionRun="10"
          maxActive="20"
          minIdle="5"
          maxIdle="20"
          maxWait="10000"
          initialSize="10"
          username="XXXXXX"
          password="XXXXXX"
          driverClassName="oracle.jdbc.driver.OracleDriver"
          defaultAutoCommit="true" 
          url="jdbc:oracle:thin:@10.48.171.20:1521:topdb15" />

</GlobalNamingResources>
Comment 4 Christopher Schultz 2018-11-16 16:59:35 UTC
... and how do you obtain connections and then use them?

The DataSourceFactory.getConnection method does not need to be synchronized. Read further and you'll find that it is indeed threadsafe.

When you say "on jboss not get the problem", do you mean that when you use JBoss and tomcat-pool you don't see this problem? Or are you using JBoss's contained-managed DataSource.

Is there a particular reason you feel like you must choose between tomcat-pool and c3p0? What about Tomcat's default pool... the one based upon dbcp2?