Here are two snips from MySQL's general log file. The first is a failed PUT of a single file using a CVS checkout from the beginning of the week. The second is a successful PUT of the same file using 2.1M1. I don't think the versions are significant, I just couldn't get my checkout install to start working again after I broke it ;).
---------------- failed -------------------
3 Query SELECT 1 FROM VERSION_HISTORY vh, URI u WHERE vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and REVISION_NO = '1.0'
3 Query commit
3 Query rollback
3 Query SET autocommit=1
3 Query SET autocommit=0
3 Query SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
3 Query select vc.CONTENT from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and vh.REVISION_NO = '1.0'
4 Connect [EMAIL PROTECTED] on slide
4 Init DB slide
4 Query select round('inf'), round('-inf'), round('nan')
4 Query SHOW VARIABLES
4 Query SET autocommit=1
4 Query SET autocommit=0
4 Query SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
4 Query select 1 from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and vh.REVISION_NO = '1.0'
4 Query delete VERSION_CONTENT from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = '1.0' and vh.URI_ID=u.URI_ID AND u.URI_STRING='/files/2nd Floor.jpg'
4 Query rollback
4 Query rollback
4 Query SET autocommit=1
---------------- successful -------------------
3 Query SELECT 1 FROM VERSION_HISTORY vh, URI u WHERE vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and REVISION_NO = '1.0'
3 Query commit
3 Query rollback
3 Query SET autocommit=1
3 Query SET autocommit=0
3 Query SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
3 Query select 1 from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and vh.REVISION_NO = '1.0'
3 Query delete VERSION_CONTENT from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = '1.0' and vh.URI_ID=u.URI_ID AND u.URI_STRING='/files/2nd Floor.jpg'
3 Query select 1 from VERSION v, URI u where v.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg'
3 Query select 1 from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = '/files/2nd Floor.jpg' and REVISION_NO = '1.0'
3 Query insert into VERSION_CONTENT (VERSION_ID, CONTENT) select vh.VERSION_ID, '[... some binary junk ...]’
----------- end logs --------------
Right off I'm seeing that the failed operation opens a second connection (the first column is the connection number) to the database to store the content *without* committing the first transaction. I'm pretty sure this is the cause of the deadlock.
The other odd behavior is that the failed PUT calls retrieveRevisionContent just before it opens the second connection to store the content. This is strange, because the file being uploaded doesn't exist, so there's nothing in the database to retrieve. I've also noticed in Slide's logs that on PUTs where a deadlock occurs GetMethod throws an IllegalStateException while trying to set the size of the response buffer (see the linked to message above).
-James
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
