Ruby DB-connection (SQLITE) with concurrent gem

Viewed 74

I have a question and already the workaround for it, but I am not sure if this is "right" solution. The followning example does not work. It gets stuck by the SQL-Update.

def mysynccmd(username,log)
    exit_code = system("Doing stuff")
    if exit_code != true
        log.error "Error: #{username} not done."
    else
        log.info "Successfully done user: #{username}"
    end
    return exit_code
end

# Create thread pool
pool = Concurrent::FixedThreadPool.new(threadlimit) 

# Running loop over the list of users with status 0
begin
    #SQLite3::Database.new(dbfile) do |db|
    db = SQLite3::Database.open dbfile
        db.execute( "SELECT user FROM T_users WHERE status = 0" ) do |user|
            # Puts work into the thread pool
            pool.post do 
                # Check if the sync was correct and update the DB
                if mysynccmd(user[0],log) == true
                   db.execute("UPDATE T_users SET status=1 WHERE user = '#{username}';")
                end
            end
        end
    #end
rescue SQLite3::Exception => e
    log.error "Exception occurred: #{e}"
ensure
    db.close if db
end

And I was thinking during the multithreading the DB-connection gets lost, because of the new process/thread.

So I did it this way and opend a new connection in every thread.

def mysynccmd(username,log,dbfile)
    exit_code = system("Doing stuf")
    if exit_code != true
        log.error "Error: #{username} is not done."
    else
        log.info "Successfully done user: #{username}"
        begin
            db = SQLite3::Database.open dbfile
            db.execute("UPDATE T_users SET status=1 WHERE user = '#{username}';")
        rescue SQLite3::Exception => e
            log.error "mysynccmd: Exception occurred: #{e}"
        ensure
            db.close if db
        end
    end
end

# Create thread pool
pool = Concurrent::FixedThreadPool.new(threadlimit) 

# Running loop over the list of not synced users
begin
    #SQLite3::Database.new(dbfile) do |db|
    db = SQLite3::Database.open dbfile
        db.execute( "SELECT user FROM T_users WHERE status = 0" ) do |user|
            # Puts work into the thread pool
            pool.post do 
                # Check if the sync was correct and update the DB
                mysynccmd(user[0],log,dbfile)       
            end
        end
    #end
rescue SQLite3::Exception => e
    log.error "Exception occurred: #{e}"
ensure
    db.close if db
end

Was my thinking right or wrong?

Thanks for your answers and/or alternative ideas.

0 Answers
Related