Active development of JAMWiki has ceased, and bug fixes and support will be limited at best. If you are interested in taking over management of JAMWiki please send an email to the jamwiki-devel mailing list.

Tech comments:Transactions

Redirected from Tech talk:Transactions

Database connections

The comment below originally from User:dallas:

So far it seems that rather than using a single connection for the entire operation, multiple connections get created and these end up locking each other. Not sure why this doesn't cause a problem on any other databases but haven't had time to look into it yet.

But I do wonder if you've considered using a different Transaction / Connection handling strategy? For example using a Servlet Filter to span a database Transaction across the life of each web request, e.g. with the Connection object being tied to the HttpServletRequest or maybe a ThreadLocal seeing as you don't pass the HttpServletRequest though your API? Something akin to using the "Open Session in View" pattern for Hibernate?

The current database connection pool is a fairly standard one, so it's surprising that HSQL and (potentially) other systems would end up locking - more than likely I may have made some bone-headed error during a late-night coding spree that caused issues. That said, even though I'm far from a database connection guru, I don't think that JAMWiki is really doing anything that's unusual for a web application with respect to database connections. As to different connection / transaction strategies, if the connection pooling code could be modularized to allow for use of different connection pools I would be 100% in support. I'd be a bit leery of ripping out the current code, but it probably wouldn't be too hard to allow users to choose from multiple pooling options in a way that allows the current code to be kept around until any bugs in new code are worked out. It's fairly late so I haven't looked into what effort might be required, but if anyone is interested in that idea it could easily be put on the roadmap for a future release. -- Ryan 12-Mar-2008 22:58 PDT

Just a note that may be relevant; in my environment I use a JNDI DataSource for my Sybase ASA database, so maybe the problem stems from using JNDI rather than the internal connection pool. Here is the abbreviated stacktrace at the time my "extra" connection gets created, which later leads to a deadlock. Note the line numbers might not match up as I have local changes to WikiDatabase

Daemon Thread [http-9080-2] (Suspended (entry into method getConnection in DatabaseConnection))	
	DatabaseConnection.getConnection() line: 225	
	WikiDatabase.getConnection() line: 62	
	WikiDatabase.getConnection(Object) line: 74	
	SybaseASADataHandler(AnsiDataHandler).lookupTopic(String, String, boolean, Object) line: 748	
	LinkUtil.isExistingArticle(String, String) line: 382	
	LinkUtil.buildInternalLinkUrl(String, String, WikiLink) line: 310	
	LinkUtil.buildInternalLinkHtml(String, String, WikiLink, String, String, String, boolean) line: 240	
	WikiLinkTag.buildInternalLinkUrl(ParserInput, int, String) line: 95	
	WikiLinkTag.processLinkContent(ParserInput, ParserOutput, int, String) line: 204	
	WikiLinkTag.parse(ParserInput, ParserOutput, int, String) line: 112	
	JAMWikiProcessor(JFlexLexer).parseToken(String, ParserTag) line: 118	
	JAMWikiProcessor.yylex() line: 1349	
	JFlexParser.lex(JFlexLexer, String, ParserOutput, int) line: 112	
	JFlexParser.parseProcess(ParserOutput, String, int) line: 250	
	JFlexParser.parseMetadata(ParserOutput, String) line: 196	
	ParserUtil.parseMetadata(ParserInput, String) line: 89	
	ParserUtil.parserOutput(String, String, String) line: 72	
	WikiDatabase.setupSpecialPage(Locale, String, String, WikiUser, boolean, Connection) line: 338	
	WikiDatabase.setupSpecialPages(Locale, WikiUser, Connection) line: 350	
	WikiDatabase.setup(Locale, WikiUser) line: 194	
	SybaseASADataHandler(AnsiDataHandler).setup(Locale, WikiUser) line: 1057	
	WikiBase.reset(Locale, WikiUser) line: 168	

-- Dallas 13-Mar-2008 03:48 PDT

Thanks, I'll take a look either tonight if time permits or else over the weekend, when I'm hoping to spend some time getting a 0.6.5 bug release out to fix a regression that broke the Special:Manage page. If you discover anything that is an "obviously correct" fix that should be included feel free to commit. -- Ryan 13-Mar-2008 08:13 PDT
OK, had a chance to check it out, and that looks like a legitimate deadlock - there is a connection being held in setupSpecialTopic, and a second connection is then taken in isExistingArticle. I suspect that this is the same issue as the one mentioned in this report. The problem isn't triggered in MySQL, HSQL, Postgres or Oracle luckily... The solution isn't immediately obvious but it will get fixed one way or another. -- Ryan 13-Mar-2008 19:49 PDT
The problem is the parsing for metadata, and that doesn't have to happen in setupSpecialPage(). Taking out the call to ParserUtil.parserOutput() and then updating metadata at the end of the setup process should solve the issue. -- Ryan 13-Mar-2008 20:19 PDT

OK, I see that would work! fyi I actually had a go at this last night & fixed the problem in my sandbox a different way, by refactoring the Transaction handling ... my idea was to make use of the Spring Framework Transaction support to demarcate a transaction around the setupSpecialPage method & having all contained database work automatically participate in that Transaction - done by changing the DatabaseConnection class to use a Spring JDBC DataSourceTransactionManager & making its getConnection method "transactionally aware" by having it delegate to Spring's DataSourceUtils#getConnection(DataSource) method. Would you be interested in this as a contribution, maybe for a 0.7.0 release? It does add a dependency on the spring-jdbc module. It may actually be a good idea wrap all the "service" methods in transactions to ensure this kind of problem doesn't creep in the future? -- Dallas 14-Mar-2008 03:00 PDT

Sounds reasonable - the "quick fix" is to fix the setupSpecialPage method, but you're right that long-term a better solution is needed. Provided the Spring transaction stuff works with JDK 1.4 (they've been pushing JDK 1.5 annotations in some of the newer stuff) it should be a good way to properly fix this problem. -- Ryan 14-Mar-2008 08:12 PDT
Hopefully revision 2100 will solve the immediate problem and a longer-term solution can be developed - the Spring transaction solution might be a good one. -- Ryan 15-Mar-2008 10:35 PDT
Ryan, I've prepared a re-work of transaction code using Spring - see Tech:Transactions for a description, let me know if I should check this into the SVN trunk. -- Dallas 07-Apr-2008 15:08 PDT
Looks good to me! I'll take a closer look after the code is merged, but this should be a nice update for the next release. As a side note, if it's OK with you let's copy this conversation to either Tech:Transactions#Comments or Tech talk:Transactions so all of the discussion is in one place. Thanks for the work! -- Ryan 07-Apr-2008 19:28 PDT
Changes checked in -- Dallas 09-Apr-2008 11:43 PDT
Thanks! It works fine on my local Postgres installation so far, and the code looks good to my (admittedly tired) eyes. I'll take a closer look over the weekend and try to run through some tests with a few different setups (MySQL, HSQL, etc) just to verify that all is well. -- Ryan 10-Apr-2008 02:10 PDT