Rails - May 2006 - AR transactions and isolation levels

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

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


On 5/19/06, Isak Hansen <isak.hansen@gmail.com>
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.
>
> 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
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

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

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

On 5/19/06, Pazu <pazu@pazu.com.br> wrote:
> 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.
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



>
> 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
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

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

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

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