Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
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. –  FreshPhilOfSO 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
show 7 more comments

1 Answer

up vote 2 down vote accepted

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

http://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.

share|improve this answer
    
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 :) –  FreshPhilOfSO Jun 29 '12 at 13:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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