Forum: Ruby on Rails Rails, Transactions, and statements

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.
Joe C. (Guest)
on 2006-05-31 22:24
Hi, I have a problem where I need an auto-incremented id back from the
database for a statement that has yet to be committed.  The operation
that uses the id may fail, and if so, I need to rollback the database.

I can't figure out how to send only the statement to the database
without the commit without sending over hard coded SQL.  This is what I
started out with:

1   def add_album
2     Album.transaction do
3       @page = Page.find(params[:id])
4       @album = Album.new(params[:album]) #creates a new Album in the
database
5
6       linking = Linking.new(:page_id => params[:id])
7       linking.linkable = @album
8
9       create_path(@album.id) #makes a directory using the album id
10
11      linking.save! #sends statement AND commits?
12      @album.save!
13
14      redirect_to :action => 'index'
15    end
16  end


The problem with this is that @album's id has not yet been populated.
Moving the create_path call past the object saves solves that, but now
the rows are committed to the database and I can't roll them back.

I'm sure there's a way to solve this, but I can't seem to figure it out
from here.  Where exactly will Rails send the statements over?  Is the
"save!" both sending statements AND committing?
Tom M. (Guest)
on 2006-05-31 23:06
(Received via mailing list)
On May 31, 2006, at 11:24 AM, Joe C. wrote:

> 2     Album.transaction do
> 3       @page = Page.find(params[:id])
> 4       @album = Album.new(params[:album]) #creates a new Album in
> the database

This is your problem. new DOESN'T create a new object in the
database, it creates
a new object which may or may not end up in the database (via save).

If you want to create a new object in the DB in one step, use create
instead.

> 5
> 6       linking = Linking.new(:page_id => params[:id])

You shouldn't need to mess with id's directory, AR will handle it via
object assignment.

> 7       linking.linkable = @album
> 8
> 9       create_path(@album.id) #makes a directory using the album id
> 10
> 11      linking.save! #sends statement AND commits?
> 12      @album.save!
> 13
> 14      redirect_to :action => 'index'
> 15    end
> 16  end

Why are you using save! ???

Wouldn't save suffice?
Joe C. (Guest)
on 2006-05-31 23:21
Thanks Tom,

Maybe I'm missing something here:

Create will send the statement AND commit, correct?  If so, that's
inappropriate since I do NOT want to commit at that point.  I want to
send the insert statement but not commit it.

Here's my problem with that:

//This succeeds
album = Album.create
//I now have an instantiated object and a new row in the DB

//this fails
linking = Linking.create
//database row not created and I can't rollback album

Does putting those both in the transaction block prevent this scneario
even when using create?  If so that's pretty nice and handles my
question.

Will any error in the transaction block, even if not a database call,
prevent a commit?  Is the rollback automagically issued?  Can I manually
call one?

"Why are you using save! ???

Wouldn't save suffice?"
I was using the save to raise an exception as opposed to just signalling
a failure... did I walk into a rails no-no?
Joe C. (Guest)
on 2006-05-31 23:38
I changed my function to this to see what would happen:

1   def add_album
2     Album.transaction do
3       #~ @page = Page.find(params[:id])
4       @album = Album.create(params[:album])
5
6       #~ linking = Linking.new(:page_id => params[:id])
7       linking = Linking.create()
8       linking.linkable = @album
9
10      #~ create_path(@album.id)
11      create_path(30)
12
13      redirect_to :action => 'index'
14    end
15  end


I hard coded 30 into my create_path function in order to cause it to
error.  The problem I feared happened:

The database rows were created, but the create_path errors.  These are
mutually dependent tasks, one can't happen without the other - if one
fails, they all need to fail.

create_path is merely a wrapper around a Dir.mkdir(path).  It is raising
a SystemCallError.  I'm still hoping I'm doing something wrong and that
the Transaction block should be rolling things back.
Tom M. (Guest)
on 2006-05-31 23:52
(Received via mailing list)
On May 31, 2006, at 12:21 PM, Joe C. wrote:

> Maybe I'm missing something here:
>
> Create will send the statement AND commit, correct?  If so, that's
> inappropriate since I do NOT want to commit at that point.  I want to
> send the insert statement but not commit it.

It's my understanding (heaven forbid I'm wrong!) that the transaction
block will only commit at the end, or rollback on an exception within.

How else could it work?

--
-- Tom M.
Tom M. (Guest)
on 2006-05-31 23:52
(Received via mailing list)
Are you using a DB that actually handles transactions
properly?

MySQL with InnoDB or Postgres, for instance?

--
-- Tom M.
Joe C. (Guest)
on 2006-06-01 00:05
Tom M. wrote:
> Are you using a DB that actually handles transactions
> properly?
>
> MySQL with InnoDB or Postgres, for instance?
>
> --
> -- Tom M.

Yup.... mySQL with Inno, eventually Oracle if I can sell my department.
This is something really fundamental to any application that deals with
databases so I'm trying to get my head around the Rails way.

Most of the other tools I've used have done stuff like this:
//sends 2 insert statements, and a delete to the database through the
transaction object
ObjectA.new (trans)
ObjectB.new (trans)
ObjectC.delete (trans)

//Then I can commit or rollback everything whenever I want:
if do_stuff() = 'good'
  trans.commit
else
  trans.rollback
end

All the automatic stuff in Rails is great and I love it, but if I can't
handle transactions, then I'm really up a creek with no paddle.... and
no boat for that matter.

I'm really guessing(hoping?) there is something wrong with my code
though!
Joe C. (Guest)
on 2006-06-01 00:28
Tom you saved the day!  I started thinking about the database type (Inno
vs other) as I couldn't believe this wasn't working for everyone else.

I started checking and all of the tables were set to Inno - except for
one which was migrated over from an older database - the album table.

Thanks again, it wouldn't havbe even been in my head to check that
without your reply.

This works 100% as expected now.  When the create_path function fails
the rollback is issued.  PHEW!
This topic is locked and can not be replied to.