Forcing a DB transaction to go through

[11175]

This is the 'sequel' to this:
http://www.ruby-forum.com/topic/193688

By 'sequel', I mean that this is actually the problem I am having.

Aldric Giacomoni wrote:

Okay, I finally got a clear look at the log, and it is indeed how it
seems - all the transactions to the database are queued until I actually
look at the database.

The DB I am using is SQLite3. How do I force ActiveRecord / SQLite /
Rails to commit / process the transaction so it happens in a better type
of real-time than that?

I'm looking for something like an update/commit/force/process/execute
statement to tell the DB to do its update now, and not next time someone
goes "What if I looked at that list of reports ?"
When I came in this morning, it was processing the background job which
runs every five minutes - I saw the DB (at 8am) go through the updates
from midnight to then. I don't know if someone had clicked to view the
report or if the buffer was full or what... But this is just
unacceptable behavior, you know?

And sadly, mysql or oracle or postgresql aren't choices for me at the
time.

[Frederick_Cheung]

This is the 'sequel' to this:http://www.ruby-forum.com/topic/193688

By 'sequel', I mean that this is actually the problem I am having.

Aldric Giacomoni wrote:
> Okay, I finally got a clear look at the log, and it is indeed how it
> seems - all the transactions to the database are queued until I actually
> look at the database.

> The DB I am using is SQLite3. How do I force ActiveRecord / SQLite /
> Rails to commit / process the transaction so it happens in a better type
> of real-time than that?

Well database transactions are supposed to be like that - once you've
sent "commit" then it's on disk (and if you're not in a transaction
then every statement is surrounded in an implicit transaction). Are
you sure that it isn't just buffering on the log file that is fooling
you ?

Fred

[11175]

Frederick Cheung wrote:

> Rails to commit / process the transaction so it happens in a better type
> of real-time than that?

Well database transactions are supposed to be like that - once you've
sent "commit" then it's on disk (and if you're not in a transaction
then every statement is surrounded in an implicit transaction). Are
you sure that it isn't just buffering on the log file that is fooling
you ?

Fred

Well, when I try to view the report, I get one of two behaviors. Ignore
the lack of 'a', it's a bug with the console I use (yay Windows).

1) It loads almost instantly. Log file shows:

Processing ReportsController#folder (for 127.0.0.1 t 2009-08-24
11:28:02) [POST]
  Prmeters: {"folder_id"=>"1",
"uthenticity_token"=>"3WKJGViOKytwH7Xn+g8Q1d4rH5xu9Q1H03tkqo4+pI="}
  Report Lod (47.0ms) SELECT * FROM "reports" WHERE
("reports"."report_pth_id" = '1')
Rendered reports/_lists (124.0ms)
Rendered reports/_lists (0.0ms)
Rendered reports/_lists (0.0ms)
Rendered reports/_folder (124.0ms)
Completed in 218ms (View: 124, DB: 47) | 200 OK
[http://loclhost/reports/folder?folder_id=1]

2) It takes longer than 'instantly'. Sometimes considerably longer.
I get the same output than in 1 but, prior to that, I get a ton of
'select' and 'update' statements... The same statements which should be
happening during the background job I have that runs every 5 minutes.

That background job outputs to the log file "#{Timestamp} - Swept for
reports." and the issue happens after I see this a couple of times with
no other activity.

When I came back from the week-end, as I was saying, I saw it do a whole
lot of time updates, every 5 minutes, on the same db entries.. When I
expect that these should have been done, you know, when the job ran!

I don't understand why it would buffer so much select/update statement
that should go out to the log, and why that'd slow down the app
anyway... Any idea?

[E_Litwin]

Well, when I try to view the report, I get one of two behaviors. Ignore
the lack of 'a', it's a bug with the console I use (yay Windows).

To fix that "Windows" issue with "a" not showing, follow the
instructions here:
http://codesnippets.joyent.com/posts/show/414

[11175]

E. Litwin wrote:

Well, when I try to view the report, I get one of two behaviors. Ignore
the lack of 'a', it's a bug with the console I use (yay Windows).

To fix that "Windows" issue with "a" not showing, follow the
instructions here:
http://codesnippets.joyent.com/posts/show/414

Thanks!
On another random note, I came back this morning and lo and behold, I
did not wait the usual 20 minutes to get updated data after clicking on
one of the report links.
Maybe it is fixed after all.