On Jan 3, 2012, at 9:52 AM, Thiago de Arruda wrote: > I need to implement a simple accounting subsytem that will record all > the transactions for future auditing. This uses two tables: > 'transactions' and 'entries'. Transaction->Entry is a parent->child > relationship. My customer has two requeriments : All the > records(entries/transaction) must be inserted in one database > transaction and he wants ensurance that no gaps will ever appear on > the ids generated by these two tables > > For the first requeriment(everything on one database transaction), I > must insert to the 'transactions' table, get the generated id and use > that as input for the 'inserts' in the 'entries' table, but everything > must be in one transaction, so I can't invoke 'commit' after inserting > to 'transactions' to get its generated id.
Not sure what the issue is for the first aspect here, do you just need to call Session.flush() ? http://www.sqlalchemy.org/docs/orm/session.html#flushing > > For the second requeriment(ensure that no gaps will happen in the > generated ids), I'm not sure its possible to acomplish, since if > something fails after the id for 'transaction' is generated, even > though that transaction will not be commited, that id will be lost, > thus creating a gap. > > I appreciate insights on how to solve either of these problems. > Thanks in advance. This is a common question, I was able to find a few mentions of it: http://stackoverflow.com/questions/8361513/sequence-without-a-gaps http://stackoverflow.com/questions/4105147/alternative-to-maxid-in-complex-primary-key It's a PITA to create an unbroken chain of integers that is resilient against transaction failures, because it essentially requires locking the entire table. Usually, sequences are used to create increasing integer values, but a key behavior of a sequence is that it never returns the same value again, regardless of transactional state - this is specifically so that no matter what happens, there is zero chance of a particular value ever conflicting with one acquired from the same sequence elsewhere. So here you need to create the id based on the max(id) of what is already in the table, and you also need to lock the table to ensure no other processes do the same thing at the same time. The second answer above suggests that a simple SELECT .. FOR UPDATE can be used here, the SQLAlchemy Query object uses with_lockmode() to do this, which...apparently is not correctly documented.... so you're basically looking to send the string "update" here: query(func.max(MyTable.id)).with_lockmode('update').scalar() I would test this approach out to ensure it is in fact concurrent based on the database in use. I'd create a script that does the above query, then waits for you to press "enter" using raw_input(). While it's waiting, run the same script in another window. Make sure the other script locks on the above query() until script #1 is allowed to proceed. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
