Hi,
I have this following code in my Turbogears which implements
nested_sets based msgboard. Each post has its own msg board, so
instead of locking the msgboard table itself, i lock the parent post's
row while editing the msgboard table.
msg_parents is the parent table.
msg_board is the table with all nested set heirarchy tables.
e
c = session.connect(None)
t = c.begin()
# Execute the insert on the msg_board_data table with lock
held on parent table's row
l = metadata.engine.text("SELECT id from %(parenttable)s
where id = :parent_id for UPDATE; "
"UPDATE %(table)s SET rgt=rgt + 2
WHERE rgt >= :insert_node_val and parent_id = :parent_id; "
"UPDATE %(table)s SET lft=lft + 2
WHERE lft >= :insert_node_val and parent_id = :parent_id; "
"INSERT INTO %(table)s (parent_id,
topic, content, depth, author_id, rank, lft, rgt, created, updated) "
"values(:parent_id, :topic, :content, :depth, :author_id, "
":rank, :insert_node_val, :insert_node_val + 1, NOW(), NOW()); "
% {'parenttable':
self.parent_table_name, 'table': table.name},
bindparams=[bindparam('insert_node_val', type=types.Integer),
bindparam('parent_id', type=types.Integer),
bindparam('depth', type=types.Integer),
bindparam('topic', type=types.String),
bindparam('content', type=types.String),
bindparam('rank', type=types.Integer),
bindparam('author_id', type=types.Integer)
]
)
l.execute(table_name=table.name, depth=self.depth+1,
insert_node_val=self.rgt,
parent_id=self.parent_id, topic=topic,
rank=self.rank, content=content, author_id=author_id)
t.commit()
I'm using mysql, and i start a transaction, so the select..update
doesn't autocommit. This way i have a lock on that row.
then i'm updating rgt/lgt values for a whole bunch of rows. Finally, i
do a commit to save all change.s
The controller exits fine without errors, but i dnot see any changes
showing in the db! its very strange issue! Here is the log i see thru
metadata.engine.echo = True
2007-04-10 23:48:15,508 sqlalchemy.engine.base.Engine.0x..74 INFO
BEGIN
2007-04-10 23:48:15,513 sqlalchemy.engine.base.Engine.0x..74 INFO
SELECT id from msg_parents where id = %s for UPDATE; UPDATE msg_board
SET rgt=rgt + 2 WHERE rgt >= %s and parent_id = %s; UPDATE msg_board
SET lft=lft + 2 WHERE lft >= %s and parent_id = %s; INSERT INTO
msg_board (parent_id, topic, content, depth, author_id, rank, lft,
rgt, created, updated) values(%s, %s, %s, %s, %s, %s, %s, %s + 1,
NOW(), NOW());
2007-04-10 23:48:15,513 INFO sqlalchemy.engine.base.Engine.0x..74 [1L,
2L, 1L, 2L, 1L, 1L, 'topic', 'sadfasdfasdfdasf', 1L, 10010, 1L, 2L,
2L]
2007-04-10 23:48:15,521 INFO sqlalchemy.engine.base.Engine.0x..74
COMMIT
anyone know whats going on wrong here?
thanks.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
