I’m trying to create a sequence with no gaps in my db (contrived
example, but should work out the same as my real one), and believe
db-managed transactions are the way to go about this.
It’s my first real ‘go’ at transactions, so I may be mistaken about
how things really work.
Anyway… Consider this example code:
Class Invoice < ActiveRecord::Base
def self.create_new
self.transaction do
self.connection.execute(“set transaction isolation level
serializable, read write”)
last = Invoice.find(:first, :order => “invoice_no desc”)
invoice = Invoice.new
invoice.invoice_no = last.invoice_no + 1
sleep(10)
invoice.save!
end
end
end
I couldn’t find an ‘AR way’ to set the isolation level, thus drop down
to execute(). Any better approaches?
If i understand correctly, under the serializable transaction level i
shouldn’t be able to read data that’s being updated by another
transaction. Still, running Invoice.create_new from two different
consoles at the same time seems to create two new Invoices, both with
the same invoice_no.
Am I mistaken about how transactions or the serializable isolation
level really work, or is it my code or environment that’s fubar?
Am using Postgres 8.1.something, so the DB should be up to the task.
You could add UNIQUE constraint for invoice_no , so update from second
console will fail, and application should be ready to handle
“ERROR: could not serialize access due to concurrent update” and retry
insert
You could add UNIQUE constraint for invoice_no , so update from second
console will fail, and application should be ready to handle
“ERROR: could not serialize access due to concurrent update” and retry insert
Guess I could have dropped the example, just needed some confirmation
that my way of doing things was ok.
My real problem requires serialized access, but concurrent updates don’t seem to fail. Any ideas why?
Am I mistaken about how transactions or the serializable isolation
level really work, or is it my code or environment that’s fubar?
Am using Postgres 8.1.something, so the DB should be up to the task.
Yup, serializable transactions doesn’t work as you expect. What they
really do
is take a “snapshot” of the database before the transaction starts, to
ensure
that any outside changes won’t be visible to that transaction.
PostgreSQL doesn’t actually serialize transactions (i.e., they still
execute
concurrently), but will throw an error at commit time if two
serializable
transactions modify the same row of data. In any way, that doesn’t help
your use
case, since you’re creating new records, not modifying existing ones.
There are two ways to fix this: the first is to keep the last invoice
number in
an auxiliary table, and update it every time you create a new invoice.
If two
concurrent serializable transactions try to update the last invoice
number,
you’ll get an error (and should probably retry the transaction).
The second, and probably easier solution, is to lock the whole table
before
reading the last invoice number:
def self.create_new
self.transaction do
self.connection.execute(“lock table invoices in exclusive mode”)
last = Invoice.find(:first, :order => “invoice_no desc”)
invoice = Invoice.new
invoice.invoice_no = last.invoice_no + 1
invoice.save!
end
end
The table lock will be released at transaction completion time. Be aware
that
this is a potential bottleneck if you’re creating invoices in very high
rate.
PostgreSQL doesn’t actually serialize transactions (i.e., they still execute
concurrently), but will throw an error at commit time if two serializable
transactions modify the same row of data. In any way, that doesn’t help your use
case, since you’re creating new records, not modifying existing ones.
Thanks a lot.
I’m going with the table locking approach. Doubt bottlenecks will be
an issue ever, and if it ever does, we’ll cope with it then.
I’m trying to create a sequence with no gaps in my db (contrived
example, but should work out the same as my real one), and believe
db-managed transactions are the way to go about this.
How about using a postgres sequence instead? They don’t inflict the
suffering that you’ll endure with row or table locking.
How about using a postgres sequence instead? They don’t inflict the
suffering that you’ll endure with row or table locking.
Guaranteed sequential numbers. DB sequences will get you crescent
numbers, but
they may not be sequential. Things like invoice numbers have to
be sequential,
with no gaps in them.
Interesting - under what condition will the sequence have a gap?
I’m unfamiliar with ‘crescent numbers’ and google is unhelpful.
How about using a postgres sequence instead? They don’t inflict the
suffering that you’ll endure with row or table locking.
Guaranteed sequential numbers. DB sequences will get you crescent
numbers, but
they may not be sequential. Things like invoice numbers have to be
sequential,
with no gaps in them.
– Pazu
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.