Forcing a DB transaction to go through

This is the 'sequel' to this:

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.

This is the 'sequel' to this:Sync list of files in DB vs. list of files in folder - Rails - Ruby-Forum

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

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?

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

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.