Hi all, The issue should be fixed now. Dimuthu is testing it now. The problem was indeed on how JDBC-Pool handles the closing of connections. Closing a connection in a pooling implementation means actually not really closing it but returning that connection to the pool. When connection is returned to the pool, that means when a user is finished using it, it should normally commit or rollback the connection to bring it to a consistent state, if the connection is dirty. The connection is dirty means, maybe someone has done some write operations, and also in some situations, like in MySQL default isolation level, which is REPEATABLE_READ, even the read operations means, they are creating a new transactional context and remembering it, and in that context, other writes done after it's initial read will not be visible later. So the user must commit or rollback the connection to bring it to a fresh state.
The above situation was the one we identified earlier in the App Factory's flow. Where it explicitly set to autoCommit=false, and do read operations. So if we get any occasion where we do not call commit after the connection is used, the connection will return to the pool, but will retain the earlier context, so when that connection is returned to another user, he will not see new write operations done after that. JDBC-Pool hasn't really acknowledged this as a bug, but in latest Tomcat 7.0.28 version, they have given us the option to control it. So as a rule of thumb, when using JDBC-Pool, we should always set the rollbackOnReturn property to true, to rollback connections after they are returned to the pool. I guess DBCP was already doing this correctly, which is why we didn't experience this before. Cheers, Anjana. On Thu, Jul 5, 2012 at 8:59 AM, Anjana Fernando <[email protected]> wrote: > Hi Dimuthu, > > On Thu, Jul 5, 2012 at 4:56 AM, Dimuthu Leelarathne <[email protected]>wrote: > >> Hi Anjana, >> >> Few minutes ago I recreated the scenario. Then I reverted um.core to use >> dbcp pooling and now it works perfectly. >> >> I know ndatasource is fantastic! But looks like there are some issues >> with it. >> >> This would be a blocker for our kernel release. Can we use a newer >> version of ndatasource? >> >> > I see, there may also be a difference in configuration on how DBCP and > JDBC-Pool works. For example, in the registry/um.core data source, I've > added a validationQueryTimeout so it will not execute the validation query > all the time. We will check it out today. Newer version of ndatasource > means, it's basically using Tomcat JDBC-Pool, and I was checking the > changelog in JDBC-Pool for the latest release, and it does not mention any > issues fixed related to our problem. Also, did you get a chance to check > the scenarios I mentioned in my earlier mail? .. As a side note, we may > want to upgrade Tomcat to the latest v7.0.28 which was released Jun 25'th, > which seems to have several bug fixes. > > Cheers, > Anjana. > > >> thanks, >> dimuthu >> >> >> On Wed, Jul 4, 2012 at 9:32 PM, Sumedha Rubasinghe <[email protected]>wrote: >> >>> Dimuthu, >>> Can you confirm if the same method is being used in following thread >>> started by Shelan as well. >>> '[Dev] Cannot log into product after creating Tennants'. >>> >>> >>> >>> On Wed, Jul 4, 2012 at 6:02 PM, Dimuthu Leelarathne >>> <[email protected]>wrote: >>> >>>> Hi all, >>>> >>>> >>>> This is a connection pooling issue. The reason for saying that is as >>>> follows. >>>> Here is the SQL = String sql = "SELECT UM_ID FROM UM_TENANT WHERE >>>> UM_DOMAIN_NAME=?" >>>> >>>> Consider the following control flow. >>>> >>>> Control Flow >>>> =========== >>>> >>>> Connection dbConnection1 = getDBConnection(); >>>> Connection dbConnection2 = getDBConnection(); >>>> prepare and execute SQL using dbConnection2 -> result is successful >>>> prepare and execute SQL using dbConnection1 -> result is unsuccessful >>>> prepare and execute SQL using dbConnection2 -> result is successful >>>> prepare and execute SQL using dbConnection1 -> result is unsuccessful >>>> >>>> Now see we are executing the same SQL using same parameter using two db >>>> connection. The dbConnection1 goes totally crazy for requests that hit the >>>> above code segment - one after the other. >>>> >>>> thanks, >>>> dimuthu >>>> >>>> >>>> >>>> >>>> On Wed, Jul 4, 2012 at 4:44 PM, Dimuthu Leelarathne >>>> <[email protected]>wrote: >>>> >>>>> Hi Sumedha, >>>>> >>>>> This is not an application level concurrency issue because how would >>>>> you explain, >>>>> >>>>> 1) With mysql_workbench while I have paused the program with a debug >>>>> point. >>>>> 2) There is concurrency associated with this scenario >>>>> 3) The reverse works. When domain is given the ID is retrieved >>>>> >>>>> My next steps, >>>>> 1) Run using h2 server >>>>> 2) Retrieve domain given the ID using same db connection >>>>> >>>>> thanks, >>>>> dimuthu >>>>> >>>>> >>>>> On Wed, Jul 4, 2012 at 4:11 PM, Sumedha Rubasinghe >>>>> <[email protected]>wrote: >>>>> >>>>>> Dimuthu, >>>>>> I was under the impression this was solved. Thought Anjana had a look >>>>>> @ it. >>>>>> This cannot be a caching issue @ the database level. Then it should >>>>>> impact everywhere. I still feel this is an application level concurrency >>>>>> issue. >>>>>> >>>>>> Let me discuss with Anjana & get back to you. Sorry for allowing you >>>>>> to live with this for long. >>>>>> >>>>>> On Wed, Jul 4, 2012 at 3:56 PM, Dimuthu Leelarathne < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> Hi all, >>>>>>> >>>>>>> This seems to be a blocker for us. Looks like a caching issue at >>>>>>> mysql and/or pooling layer. I tried without the following indexing and >>>>>>> still get the same results. I don't think it is a mysql layer because >>>>>>> the >>>>>>> same SQL is running on mysql_workbench. >>>>>>> >>>>>>> CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME >>>>>>> ON UM_TENANT (UM_DOMAIN_NAME); >>>>>>> >>>>>>> thanks, >>>>>>> dimuthu >>>>>>> >>>>>>> >>>>>>> On Mon, Jul 2, 2012 at 12:51 PM, Dimuthu Leelarathne < >>>>>>> [email protected]> wrote: >>>>>>> >>>>>>>> Hi all, >>>>>>>> >>>>>>>> AppFactory setup done last hour shows the same error again. >>>>>>>> >>>>>>>> thanks, >>>>>>>> dimuthu >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Jun 28, 2012 at 7:44 PM, Dimuthu Leelarathne < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Hi all, >>>>>>>>> >>>>>>>>> I believe what is happening is AppFactory is trying to read the >>>>>>>>> uncommitted data from the db. The attached screenshot explains the >>>>>>>>> scenario >>>>>>>>> nicely. I kept adding test1,test2,test3 .... test8 and out of these >>>>>>>>> only >>>>>>>>> even numbers are actually added - odd ones are failing. >>>>>>>>> >>>>>>>>> But after that I stopped the server did a "select *" before doing >>>>>>>>> the "select UM_ID from where *" in user.core. Then replaced the jar >>>>>>>>> and >>>>>>>>> restarted the server. As you can see test9, test10, test11 and test12 >>>>>>>>> are >>>>>>>>> added without any issue at all. Meaning doing a "select *" somehow >>>>>>>>> forced >>>>>>>>> "select UM_ID from where *" to work correctly. Looks like we are >>>>>>>>> doing some >>>>>>>>> dirty reads. >>>>>>>>> >>>>>>>>> thanks, >>>>>>>>> dimuthu >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, Jun 28, 2012 at 5:37 PM, Dimuthu Leelarathne < >>>>>>>>> [email protected]> wrote: >>>>>>>>> >>>>>>>>>> Hi all, >>>>>>>>>> >>>>>>>>>> This is fun. I am wondering whether I should a screen recording >>>>>>>>>> of this event. >>>>>>>>>> >>>>>>>>>> thanks, >>>>>>>>>> dimuthu >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> /sumedha >>>>>> +94 773017743 >>>>>> >>>>> >>>>> >>>> >>> >>> >>> -- >>> /sumedha >>> +94 773017743 >>> >> >> > > > -- > *Anjana Fernando* > Associate Technical Lead > WSO2 Inc. | http://wso2.com > lean . enterprise . middleware > -- *Anjana Fernando* Associate Technical Lead WSO2 Inc. | http://wso2.com lean . enterprise . middleware
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
