Brian Candler wrote:
I have another case of broken transaction support in ruby-dbi. (Yes, I am
doing some serious unit-testing here! :-)
Using a postgres database, I am doing something like this in two concurrent
clients:
db.transaction do
v = db.select_one("select nextval from sequences where name='test' for
update")[0]
db.do("update sequences set nextval=? where name='test' and nextval=?",
v+1, v)
sleep 3 # start the second client during this time
end
Now, what *should* happen is that 'select .. for update' acquires a row
lock. The second client is held off at that point until the first
transaction completes, and it sees the value which the first transaction
wrote. It works in DBD:Mysql.
This is broken in DBD:Pg, and the reason is here:
if not SQL.query?(boundsql) and not @db['AutoCommit'] then
@db.start_transaction unless @db.in_transaction?
end
In other words, the DBD does not issue a "BEGIN" command until the first
*non-query* SQL command. As a result, the 'select .. for update' takes place
outside a transaction, and the semantics are broken.
This is another good example why SQL parsing should be avoided by all means.
I think this is a case of premature optimisation, and the correct code
should say:
if not @db['AutoCommit'] then
@db.start_transaction unless @db.in_transaction?
end
I would even go further and drop the in_transaction and AutoCommit
checks completely. If I write transaction...do...end, I (and probably
everyone else) expect nothing more than a BEGIN, yield, COMMIT/ROLLBACK,
everything else is only confusing.
|
