0

It looks like I lack some basic understanding of how SQL databases work, specifically MySQL. (Edit: for now I am using the InnoDB engine.) I have a thread in a program written in pure Groovy (no Grails) that does CREATE TABLE using groovy.sql.Sql, which is supposed to be a wrapper around JDBC. My question is, after this call returns, can I immediately access this table from another thread, or does it depend on luck? Do I have to call e.g. sql.close() or do I have to access the table from the creating thread first, as then logically it will have to be created?

I know that, for example, if I have a transaction, then the statement may not have an effect immediately... or so I think. Even if I don't create a transaction explicitly, who knows if Groovy or JDBC will create one for me. If I am using Hibernate or some other framework, it can also do whatever it considers optimal. My impression is that even sqlalchemy in python may do some optimization of this kind, unless I tell it not to. Similarly, what if MySQL itself is somehow caching my statement for future execution, for performance optimization, and not executing it right away?

Are there any formal rules or principles such as ACID that allow me to have some certainty? How do I know when a SQL statement is done so I can use its result? Same applies to other languages and database types, of course.

Sergey Orshanskiy
  • 6,794
  • 1
  • 46
  • 50
  • Are you using InnoDB engine? – mvp Oct 12 '13 at 21:03
  • @mvp: Yes. Does it matter for the answer? – Sergey Orshanskiy Oct 12 '13 at 21:03
  • 1
    It does, because InnoDB is fully transactional (unlike MyISAM) – mvp Oct 12 '13 at 21:04
  • Short answer is: yes, it depends on luck. If another thread makes a call when your transaction hasn't completed yet, it will not see the new table. Actually creating tables online isn't one of the best practices, are you sure you need to do that? – Ashalynd Oct 12 '13 at 21:05
  • @Ashalynd: no, I can avoid it. Wait, let me read http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html first... still, how do I know then that my transaction has completed so I can tell another thread to proceed? – Sergey Orshanskiy Oct 12 '13 at 21:06
  • What exactly are you trying to achieve? Can't you mitigate race conditions in your code somehow? I mean, if another thread is periodically checking stuff in your DB then it should not matter whether it sees the changes now or 2 minutes later. Otherwise, you can have something like a callback. – Ashalynd Oct 12 '13 at 21:09
  • @Ashalynd: in this case I can just make the user start my program separately for configuring everything, and this is what I may end up doing anyway. But I thought I might as well understanding it well this time as there are more things that I want to do in the future (e.g. have multiple threads/applications update the database) and right now I am not comfortable with the concurrency issues. When I had to deal with such things in production at my last job, we often just ignored the concurrency issues :-P and then sometimes I had to figure out what happened. – Sergey Orshanskiy Oct 12 '13 at 21:24
  • There are a lot of things to take into account there. For example, even if your transaction succeeded, but you inserted the record on the master, and your client is using the slave to read the data, then it might not be able to see these new data due to replication delay. I would probably try to ensure that the client can check the state of things and then proceed accordingly. – Ashalynd Oct 12 '13 at 21:35

1 Answers1

2

Yes, you can use table immediately after creating it, but there are some conditions.

If you are doing it in the same transaction, there are no limitations.

If you are doing it in separate thread, then two conditions must be true:

  • Transaction that used CREATE TABLE must commit.
  • Thread that is trying to make use of new table must start another transaction after first one is committed.

You can mitigate this by always using CREATE TABLE IF NOT EXISTS.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • Thank you! 1. Is it guaranteed that different threads use different connections and are thus not in the same transaction? 2. How do I know if/when a transaction commits if I am using groovy.sql which a wrapper for JDBC, which is a wrapper for sql? I probably need to understand how both of these work, don't I? 3. When I commit a transaction explicitly and after the commit returns, does it mean that I am now 100% safe? – Sergey Orshanskiy Oct 12 '13 at 21:20
  • 1. It is not, and depends on your app. Normally you want to have [connection per thread](http://stackoverflow.com/a/1456878/1734130). 3. Yes, if transaction commits, and only *then* you start another one, you are safe. – mvp Oct 12 '13 at 21:25
  • Great... I'll read more on the subject. It looks like JDBC keeps the autocommit mode by default: http://stackoverflow.com/questions/4940648/how-to-start-a-transaction-in-jdbc. By the way, how did you insert a link in the comment? – Sergey Orshanskiy Oct 12 '13 at 21:43
  • use this for links: `[text](url)` – mvp Oct 13 '13 at 02:21