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'

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?


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.


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!

  • Worth mentioning - I also did a lot of research looking into "pre-commit" triggers. It appears those do not exist, so unfortunately that's not an option either. – Jake Feasel Jun 7 '12 at 23:13
  • 2
    For Oracle, this seems like a good sneaky way of catching COMMITs: stackoverflow.com/a/6463800/790702 What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring. – Philᵀᴹ Jun 7 '12 at 23:37
  • @Phil the only problem there is that I don't have (and don't really want) control over the definition of each of the tables (those are defined on the left panel). So that DEFERRABLE INITIALLY DEFERRED clause wouldn't be there (unless there is some way to do that automatically for all tables - say via a system trigger that responds to create table statements? Ugh) – Jake Feasel Jun 7 '12 at 23:45
  • 1
    @NickChammas I need to disable commits so that I can keep the schema (as defined by the left side panel) in a fixed state. There could be any number of people writing queries against the same schema trying to solve the same problem. To prevent them from interfering with each other, I need to be sure to keep the structure and data from changing. This is accomplished via rolled-back transactions, but that doesn't happen if the right-side code is committed. – Jake Feasel Jun 8 '12 at 4:48
  • 2
    @RickJames DDL creates implicit commits in MySQL and Oracle, but not in PostgreSQL or SQL Server. The mechanisms I've implemented following this post handle that concern. As far as entering arbitrary SQL - that's the whole point! – Jake Feasel Jun 20 '12 at 23:23

For Oracle, this seems like a good sneaky way of catching COMMITs:


What he doesn't mention is that you should be able to catch the constraint violation in your code too, to stop the 2nd situation occurring.

  • Great, thanks again Phil. I have been able to make good use of this technique for Oracle. It makes me wish other databases supported deferred constraints. – Jake Feasel Jun 28 '12 at 16:42
  • No worries. Drop into chat and say hi :) – Philᵀᴹ Jun 29 '12 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.