Hi 2011/7/29 Michael Wood <esiot...@gmail.com>
> On 29 July 2011 14:01, Ken Wesson <kwess...@gmail.com> wrote: > > On Fri, Jul 29, 2011 at 7:41 AM, László Török <ltoro...@gmail.com> > wrote: > >> Hi, > >> > >> is there a way to execute multiple select queries on a table using > clojureql > >> in a transaction? > >> > >> sg. like > >> > >> (with-results [res1 query1 > >> res2 query2] > >> .. do sg) > >> > >> and res1 and res2 would be taken from a same consistent snapshot. > > > > If you run two separate queries, I expect the database server will see > > them as two separate transactions, and may interleave some other, > > mutating transaction between them, so they won't in that case be taken > > from a consistent snapshot. > > Well, I suppose it you use SELECT FOR UPDATE/SHARE it should not allow > changes between them, but I've never used SELECT FOR UPDATE or SHARE, > so perhaps I'm wrong or I'm misunderstanding your point. > I'm not sure SELECT FOR UPDATE/SHARE is very portable and whether it maps to relational algebra primitives which are the underpinnings of ClojureQL, if I am correct. It also introduces a lock which is not my intention. I need to make sure the when I'm done with the operation I can insert a new row into the table and be sure certain invariants still hold. My approach is to define a uniqueness constraint on a sequence_no coloumn. If an insert with MAX(sequence_no)+1 succeeds, I guess I can be sure nothing changed while I was busy computing the result. Anyway, to be clear, I'm ok with my approach, I just didn't know how to do it in ClojureQL. :) > > e.g. > > > http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE > > http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html > > So, I think this should work: > > BEGIN; > SELECT ... FOR SHARE; > SELECT ...; > COMMIT; > > Whether it's possible to do that with ClojureQL I have no idea. > > > You'd need to have a single query to the DB, which will run as a > > single transaction, generate both res1 and res2. I'm not sure if that > > will always be possible; my SQL-fu is a little rusty. > > -- > Michael Wood <esiot...@gmail.com> > > -- > You received this message because you are subscribed to the Google > Groups "Clojure" group. > To post to this group, send email to clojure@googlegroups.com > Note that posts from new members are moderated - please be patient with > your first post. > To unsubscribe from this group, send email to > clojure+unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/clojure?hl=en > -- László Török Skype: laczoka2000 Twitter: @laczoka -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
