On Jun 28, 1:40 am, "Uwe C. Schroeder" <[EMAIL PROTECTED]> wrote:
> On Wednesday 27 June 2007, Michael Bayer wrote:
>
> > this issue can be worked around by using explicit transactions.
>
> actually no, it can't. Maybe I don't get it right, but the only way for me to
> get a commit was actually to modify Connection._autocommit in
> sqlalchemy.engine.base.
either TG is getting in the way, or youre not getting it right. if
anyone ever needs to modify the internals of SA to get something to
work, i would *highly* prefer if they could email the ML or post a
trac ticket with their issue so that it may be handled properly.
> Obviously SA thinks there is no transaction in TG,
> so it just wraps one around it.
if TG actually has "a transaction" going on, theyd certainly have to
configure SA to be aware of it (most likely via SessionTransaction).
if not, then yes things arent going to work at all (though still, an
explicit SA transaction should work all by itself).
> I agree, and that is certainly DB dependent. Personally I can't imagine that
> an automatically issued rollback for every select transaction is in any way
> more overhead than issuing a commit. Not wrapping a select in a transaction
> will definitely be the least overhead.
we dont issue a rollback for every select transaction. we issue a
rollback when a connection is returned to the pool. you can check out
a connection explicitly and perform any number of selects on it
without any rollbacks or commits.
because the "rollback" is at the connection-pool checkin level, it
should be more apparent how inappropriate it would be to issue a
*commit* every time a connection is returned to the pool, an operation
that knows nothing about what just happened with that connection. the
rollback is to release database locks.
im thinking that it might be time to allow an option in SA that just
turns the DBAPI's "autocommit" flag on. that way you can just blame
the DBAPI for whatever issues arise.
its not always possible to "not wrap a select in a transaction".
oracle for example *always* has a transaction going on, so everything
is in a transaction in all cases.
> > that a stored-procedure-oriented application is "far more efficient"
> > is *extremely* debatable and database-dependent as well.
>
> I doubt it's *extremely* debatable.
its extremely debatable:
http://www.google.com/search?q=stored+procedures+vs
> Just issue 100 inserts from inside a
> stored procedure (or 100 updates) and do the same discretely with any kind of
> db interface. In case of the interface every statement has to be parsed by
> the db, whereas in a stored procedure the statement is already "compiled" of
> sorts (at least Oracle and PostgreSQL do that).
the debate over SP's is about a larger issue than "is an SP faster
than 5 separate INSERT statements". SP's are of course much better
for micro-benchmarks like that. its their impact on application
development and architecture where the debate comes in (read some of
the googled articles).
I am certainly not anti-SP, ive done pure SP applications before (on
projects where the DBAs controlled the SPs)...I just dont want to
start hardwiring SQLAlchemy to expect that sort of application. I
think 80/20 as applied to SELECT is that 80% of SELECTs are for read
operations and a COMMIT is inappropriate.
> > if you
> > really want COMMIT for every SELECT, i'd favor it being enabled via an
> > option passed to create_engine().
>
> Not every select, every transaction that didn't roll back.
> I just think the default of rollback on every transaction is wrong - a
> rollback should occur when there is a problem, not when the transaction was
> fine. But that may just be me.
this is the use case:
c1 = pool.connect()
row = c1.cursor().execute("select * from sometable").fetchone()
pool.return_connection(c1)
c2 = pool.connect() # (returns a connection that is not c1)
c2.cursor().execute("drop sometable") # --> deadlock
if DBAPI supported a "release_locks()" method, we'd be using that.
> Probably because a lot of people can't figure out how to use stored procedures
> and triggers, since the "lightweight/open-source" programming is often done
> on a database that has very limited support for both :-)
keep in mind youre including the vast Hibernate community, including
its creators, etc. im not sure if the "im to dum to use stored
procedures" argument can fully explain why the SP-architecture remains
a minority use case. i think the overall inconvenience of it, the
clunky old languages you have to use on DBs like Oracle and SQL
Server, as well as the harsh resistance it puts up to so-called agile
development methods are better reasons.
> Personally I'm not a big fan of handling database integrity outside the
> database.
> ....continue SP arguments.....
thats great, you can have your preferences..the google link above
should reveal that quite a few people have established their
preferences in this matter. If you are truly writing an SP-only
application which prevents direct SQL access (that was the kind of SP
app I worked on), a tool like SQLAlchemy is mostly superfluous.
for my purposes as the maintainer of SQLAlchemy, i need to support the
majority of use cases which is that of a non-SP oriented application.
not just because SQL-oriented apps are more common, but also because
SP-oriented apps aren't going to have use for higher level SQL
toolsets anyway since all the SQL is behind stored procedures.
> the end). In the end I think it's a SA issue - there should be a parameter
> allowing to tell SA that this "select" is actually something transactional
> and needs to commit if no error is raised.
this is this:
> michael bayer wrote:
> i
> also have a notion of SQL functions being marked as "transactional" to
> help this issue.
but really, i think you should get your explicit transactions
working...i would imagine you have the need to execute multiple SPs in
one transaction (otherwise that must be some enormous SP youre
running).
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en
-~----------~----~----~----~------~----~------~--~---
