[sqlalchemy] Transactions
I use transactions and sometimes it must be *session.rollback*. Sometimes i get a AttributeError: 'NoneType' object has no attribute 'twophase' That error raises, when one transaction open and processed, and *another* transaction with session use *rollback* -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Transactions and read-only operations
On Sat, 7 Jun 2008 22:21:20 -0700 (PDT)
Tomer [EMAIL PROTECTED] wrote:
I have two follow-up questions:
1) From what I understand, if I read an object (eg, Student) from
the database, modify that object or other objects, and then commit, I
have no guarantee that the object didn't change between the time I
read it and the time I committed.
Well, I think I can describe PostgreSQL's behavior as I understand it, I
would guess other DBMSs have reasonably similar semantics. I can't speak
to whether SA has any precautions of it's own above and beyond what the
DBMS provides, but I would tend to doubt it.
In the case of selecting a Student, altering it, and flushing it back
all within one transaction: If a concurrent transaction has uncommitted
UPDATEs or DELETEs on any rows you try to UPDATE or DELETE, your
statement will block until the concurrent writer commits or rolls back.
Then, either
A) You are using read committed isolation (allows nonrepeatable
reads), in which case it then runs the statement against the new view
of the world, or
B) You are using serializable isolation (no nonrepeatable reads), in
which case it aborts your transaction if the other writer changed the
rows you were trying to write.
Whether nonrepeatable reads cause breakage depends on the specific
nature of the updates.
For example:
if len(Session.query(Student).filter_by(Student.name ==
'Bill').all()) 0: # are there any students named 'Bill'?
school = Session.query(School).one() # assume there is one
school in the database
school.hasStudentNamedBill = True
Session.commit()
When the commit is issued, I might end up with an inconsistent
database, because a different transaction (in another thread, for
example) may have changed the name of the student after I checked if
there is a student named Bill, but before I committed.
This is interesting, and I think it's where SELECT FOR SHARE (or
similar) can help. In the non-contended case it's mostly just a normal
SELECT. If a concurrent transaction has uncommitted UPDATEs or DELETEs
on any rows selected by this statement, the SELECT FOR SHARE will wait,
as above, until the concurrent writer commits or rolls back. Then, it
either
A) (Read committed isolation) Re-runs the SELECT to get the new view
of the world and returns that, or
B) (Serializable isolation) Aborts your transaction if the other
writer changed any of the rows SELECTed.
In SA I think it would look like this, modulo note [1]:
if len(Session.query(Student).with_lockmode('read')
.filter_by(Student.name == 'Bill').all()):
# ...and so on
It is also possible to use SELECT FOR SHARE NOWAIT to request that the
DB abort your transaction if the operation would have blocked. SA
doesn't implement NOWAIT except on SELECT FOR UPDATE, as far as I can
tell.
From the last
answer it seems like databases that support transactions might not
suffer from this problem (if they take locks on SELECTed rows), but I
tried in SQLite (which supports transactions) and it didn't help.
Would a different database help solve the problem?
That would probably be the case. I have never attempted to use SQLite in
a high-concurrency situation, and I get the impression it's not
particularly attuned for it: SQLite's lock granularity is the entire
database. It doesn't support SELECT FOR UPDATE/SHARE or anything like
it that I can tell.
-Kyle
[1] I happened to notice in the process of writing this email
that SA's PG dialect doesn't support select(for_update='read')
a.k.a. SELECT FOR SHARE. Bug?
http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-FOR-UPDATE-SHARE
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Transactions and read-only operations
If I am just issuing a read-only query, and I want to make sure that nothing will change in the middle, do I need a transaction? If so, what do I do with it when I'm done - commit or rollback? For example, I want to do this: for user in Session.query(User): print user.name Should I use a transaction like this: Session.begin() for user in Session.query(User): print user.name Session.commit() If not, how would I deal with this if my session (created with scoped_session) is transactional? Thanks, Tomer --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] transactions
Hi all,
I have a question about transactions
I need to insert data into two tables (anag and user), sice I need to
insert the generated primary key of anag into user.
I have to flush anag to have the id to insert into user table.
What happens if there's an interruption in the middle of this, for
example, immediately after to flush anag?
Should I use create_transaction() to avoid a such thing?
#transaction = session.create_transaction()
#try:
anag = Anag(
name = data.get('display_name')
)
session.save(anag)
session.flush()
user = User(
anag_id = anag.id
)
session.save(user)
session.flush()
#transaction.commit()
#except:
#transaction.rollback()
jo
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Transactions
Hi,
The following program outputs 1; I thought it should output 0. Any comments?
from sqlalchemy import *
db = create_engine(sqlite:///:memory:)
metadata = BoundMetaData(db)
users = Table('users', metadata,
Column('user_id', Integer, primary_key=True),
Column('user_name', String))
users.create()
c1 = db.connect()
t1 = c1.begin()
c1.execute(users.insert(), user_id=1, user_name='user1')
c2 = db.connect()
t2 = c2.begin()
print len(c2.execute(select * from users).fetchall())
Regards,
Paul
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
