Forum: Ruby on Rails AR transactions and isolation levels

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
5233478c51a92b6a1a5c970cbf3a42f3?d=identicon&s=25 Isak Hansen (Guest)
on 2006-05-19 11:47
(Received via mailing list)
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.

Any feedback appreciated,
Isak
5c19f2d52879a1e10670c7334ba4c7e3?d=identicon&s=25 Lugovoi Nikolai (Guest)
on 2006-05-19 12:21
(Received via mailing list)
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
5233478c51a92b6a1a5c970cbf3a42f3?d=identicon&s=25 Isak Hansen (Guest)
on 2006-05-19 13:01
(Received via mailing list)
On 5/19/06, Lugovoi Nikolai <meadow.nnick@gmail.com> wrote:
> 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?

Isak
6e09635022712a6dd26b9510a2c96820?d=identicon&s=25 Pazu (Guest)
on 2006-05-19 15:34
(Received via mailing list)
Isak Hansen <isak.hansen@...> writes:

> 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.

-- Pazu
5233478c51a92b6a1a5c970cbf3a42f3?d=identicon&s=25 Isak Hansen (Guest)
on 2006-05-19 16:59
(Received via mailing list)
On 5/19/06, Pazu <pazu@pazu.com.br> wrote:
> 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.

Isak
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-19 20:00
(Received via mailing list)
On May 19, 2006, at 2:44 AM, Isak Hansen wrote:
> 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.

jeremy
6e09635022712a6dd26b9510a2c96820?d=identicon&s=25 Pazu (Guest)
on 2006-05-19 20:46
(Received via mailing list)
Jeremy Kemper <jeremy@...> writes:

> 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
24d2f8804e6bb4b7ea6bd11e0a586470?d=identicon&s=25 Jeremy Kemper (Guest)
on 2006-05-19 21:17
(Received via mailing list)
On May 19, 2006, at 11:45 AM, Pazu wrote:
> Jeremy Kemper <jeremy@...> writes:
>> 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.

Could you explain?

Thanks!
jeremy
This topic is locked and can not be replied to.