Mysql – Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't inadvertently make the potential abuse worse, but what can you do? I trust you folks.

I would like to prevent any user from issuing explicit 'commit' calls within a given transaction block. From the context of SQL Fiddle, the transaction block is the code that is executed on the right-side panel. Basically, I'm looping over and executing a list of plaintext SQL commands, and I want to ensure that all changes they make will be rolled back at the end of the batch. Normally, their changes do get rolled back, but sometimes there is an explicit 'commit' statement within the text, and so of course my rollback won't work. This explicit commit is very likely from a user attempting to break the schema on SQL Fiddle, so other people working on it will see errors.

Main Desired Result: I would like to disable explicit commits at the JDBC level, if possible. This is because I have to support multiple database backend vendors, and of course each has their quirks at the low level.

Fallback option: If it's not possible to configure JDBC to disable explicit commits, then I'd be open to solutions for detecting explicit commits while processing a batch for each of the following backends: SQL Server, Oracle, MySQL, and PostgreSQL.

For SQL Server, I thought of this solution: parse through the XML query plan for the statement before I execute it, and check for the presence of an entry that matches this XPath:

//*[@StatementType="COMMIT TRANSACTION"]

I think this would work pretty well for SQL Server. However, this approach doesn't work for the other DB types. Oracle's XML execution plan output for explicit commits makes no reference to the fact that that you're running a commit statement (but rather simply repeats the execution plan output from the queries it's committing). PostgreSQL and MySQL don't provide any execution plan output at all (XML or otherwise) for explicit commits.

That leaves me with checking the actual statement for the word "commit". This would work, except there can be all kinds of variations possible:

declare @sql varchar(50)
set @sql = 'com' + 'mit'
exec(@sql);

The above is an example for SQL Server (which I could work around), but I would guess similar things would be possible for Oracle, MySQL, and PostgreSQL. Am I wrong on that assumption? Maybe they wouldn't allow "dynamic" commit statements? Feel free to use SQL Fiddle (preferably not the sample schema or one someone else is likely to be working on) to see if you can make something similar happen in Oracle, MySQL, and PostgreSQL. If not, maybe simple string detection might work for those.

Yet another possibility

Another option occurred to me – if you know of a way to set any of these databases in a readonly mode, such while in that mode nothing could be committed, that could work too. I would need to still allow transactions to be started and code to run within them, just so long as nothing could be committed while in that mode. Is that possible?

Update

What I've learned recently – this actually isn't an issue with PostgreSQL. Apparently commits issued within a transaction block won't apply if that same block eventually gets rolled-back (in Postgres). So Hooray for Postgres!

Thanks to Phil's link to the SO post, I think I can use the DEFERRABLE INITIALLY DEFERRED hack for Oracle to accomplish what I'm after (an error will be thrown if a commit is issued, but I can work around that). This should address Oracle. (I thought for a moment that nested transactions might work here, but it doesn't look like Oracle supports nested transactions? Anyway I couldn't find anything that worked this way).

Don't really have a solution for MySQL yet. Tried using nested transactions, but that doesn't appear to work. I'm seriously thinking of more drastic approaches for MySQL, such as either not allowing anything but SELECTs on the right-side or dropping/recreating the DB after each query. Neither sound good though.

Resolution

So, I have now implemented the described solutions for SQL Server and Oracle, and as I mentioned this isn't actually a problem for PostgreSQL. For MySQL, I have taken the somewhat unfortunate step of restricting the query panel to only select statements. DDL and DML for MySQL will simply have to be entered on the schema panel (the left-side). I hope this doesn't break too many old fiddles, but I think it simply is what has to be done to ensure data consistency. Thanks!