# Transaction when operating in two tables

**URL:** <https://www.ruby-forum.com/t/transaction-when-operating-in-two-tables/122640>\
**Category:** Rails\
**Created:** [December 10, 2007, 10:40am UTC](https://www.ruby-forum.com/t/transaction-when-operating-in-two-tables/122640 "2007-12-10T10:40:46Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![raimon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@raimon](https://www.ruby-forum.com/u/raimon)\
**Post date:** [December 10, 2007, 10:40am UTC](https://www.ruby-forum.com/t/transaction-when-operating-in-two-tables/122640/1 "2007-12-10T10:40:46Z")

</div>

Hello,

I’m reading from a table in the database to fetch some path to files to  
import.

I do a loop for each path, and for each .csv file, I update another  
table.

All works perfect, but it takes lot of time, 90 files and aprox.  
2.000.000 rows.

After adding transactions, the speed has been great, 400% faster.

Now, as I see I have to specify the table for wich the transaction  
occur, where I have to use, the firsst table (where I fetch the path to  
files) or from the other one ?

I update the path file row with a timestamp, rows created/updated/… ,  
so if I problem occurs with some file, I don’t want to update their  
status as done nor having any new data in the other table.

for your info:

table master =\> has the path files  
table dades =\> has de data  
database backend =\> sqlite (only has only one transaction, and not per  
table, per database)

thanks,

raimon

## code:

def import\_file\_nc8\_ajax

```
@txt_to_return = ""
@txt_rows_imported = 0

# delete all the rows in Master for nc8 ...
@result = Arxiu.find_by_sql('DELETE FROM masters')

@txt_to_return += "<p><b>Deleted all rows from NC8 table</b></p>"

@columns = Arxiu.content_columns # id, nc8, nacer

@file_paths=Arxiu.find(:all, :conditions => 'kind="nc8"')

require 'csv'

for arxiu in @file_paths

  @rows_per_file = 0

```

begin

```
Master.transaction do

CSV.open(arxiu.file_path, "r") do |row|

    @data_new=Master.new
    @data_new.nc8 = row[0]
    @data_new.nacer = row[1]
    @data_new.save

    @txt_rows_imported += 1
    @rows_per_file += 1

  end # CSV.open... do

end # transaction

```

rescue

end # begin

```
 arxiu.update_last=Time.now()
 arxiu.update_rows = @rows_per_file
 arxiu.save

```

end

```
  @txt_to_return += "<p><b>Successfully created " +

```

@txt\_rows\_imported.to\_s + " rows

"  
@txt\_to\_return += "

**from " + @file\_paths.size.to\_s + " files**

"

```
render(:text => @txt_to_return)

```

end # import\_file\_nc8\_ajax

* * *

ah, the question …

the question is, where I have to put the

begin

```
Master.transaction do

```

and if I have to use the Master table or the Dada table

if there are erros, the 99.99% will be in the Dada table, related to the  
imported data from the .csv file …

thanks,

rai

---

<div class="post-metadata">

**Author:** ![raimon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@raimon](https://www.ruby-forum.com/u/raimon)\
**Post date:** [December 10, 2007, 10:50am UTC](https://www.ruby-forum.com/t/transaction-when-operating-in-two-tables/122640/2 "2007-12-10T10:50:04Z")

</div>

On 10 Dec 2007, at 09:40, Raimon Fs wrote:

> All works perfect, but it takes lot of time, 90 files and aprox.  
> 2.000.000 rows.
> 
> After adding transactions, the speed has been great, 400% faster.
> 
> Now, as I see I have to specify the table for wich the transaction  
> occur, where I have to use, the firsst table (where I fetch the path  
> to  
> files) or from the other one ?

In your case, it doesn’t matter. In theory, different models can be  
using different connections, so Master.trasaction means ‘start a  
transaction on the connection that Master users’  
Since you’re not using different dbs, it doesn’t matter. However you  
should note that rails doesn’t allow you to write transactions that  
cover multiple databases. In your particular case the speed game  
probably comes from not having to flush to disk after each row update,  
so if all you care about is the speed then putting the transaction on  
Master would be the write thing since that’s where all the writes are.

Fred

---

<div class="post-metadata">

**Author:** ![raimon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/59ef9b/32.png) [@raimon](https://www.ruby-forum.com/u/raimon)\
**Post date:** [December 10, 2007, 10:59am UTC](https://www.ruby-forum.com/t/transaction-when-operating-in-two-tables/122640/3 "2007-12-10T10:59:46Z")

</div>

Frederick C. wrote:

> On 10 Dec 2007, at 09:40, Raimon Fs wrote:
> 
> > All works perfect, but it takes lot of time, 90 files and aprox.  
> > 2.000.000 rows.
> > 
> > After adding transactions, the speed has been great, 400% faster.
> > 
> > Now, as I see I have to specify the table for wich the transaction  
> > occur, where I have to use, the firsst table (where I fetch the path  
> > to  
> > files) or from the other one ?
> 
> In your case, it doesn’t matter. In theory, different models can be  
> using different connections, so Master.trasaction means ‘start a  
> transaction on the connection that Master users’  
> Since you’re not using different dbs, it doesn’t matter. However you  
> should note that rails doesn’t allow you to write transactions that  
> cover multiple databases. In your particular case the speed game  
> probably comes from not having to flush to disk after each row update,  
> so if all you care about is the speed then putting the transaction on  
> Master would be the write thing since that’s where all the writes are.
> 
> Fred

ok, thanks.

here I’m using only one connection for all models, and just only one  
database, I wasn’t aware that Table.transaction opens a transaction for  
the connection of that table, I don’t know wahy I thought it had  
something about the table that opened it, now I see that is a  
transaction for the connection that this table/model uses.

thanks for the clarification

regards,

rai