Hello jeremy. Thank you for your comment.
I wrote test program. It run two readers and writers. It access same table. Almost operation complete < 0.005 sec. But sometime raise SQLite3::BusyException. so It take over 5sec. (red part). I have no idea why this operation lock database over 5sec. That's why I decrease max_connection to 1. If I changed max_connections to 1. It works good. -- Hiroyuki Sato. 2015年4月4日土曜日 0時28分22秒 UTC+9 Jeremy Evans: > > On Friday, April 3, 2015 at 2:49:46 AM UTC-7, Hiroyuki Sato wrote: >> >> Hello members. >> >> Question >> >> I'm using Sequel with Celluloid(https://celluloid.io) >> Celluloid is a multithread library for Ruby. >> >> And SQLite db is the file database. not on-memory db. >> >> I got SQLite3::BusyException, so I would like to avoid this exception. >> >> It is possible to access sqlite db from one thread at once? >> Or should I access to sqlite from one thread? >> >> I changed those values , It seems good. >> max_connections: 1 >> pool_timeout: 10 >> >> Should I change another parameters? >> >> Is there any best practice configuration for connect sqlite3 with multi >> thread? >> > > If you are only running a single process and you don't mind blocking > queries in other threads (basically serializing access to the database), > that should be fine. > > For anything involving multithread or multiprocess access, it may be > better to set a higher busy timeout on the underlying connection objects > via the :timeout option (the default is 5000 milliseconds). > > Thanks, > Jeremy > Sequel.migration do change do create_table(:artists) do primary_key :id String :name, :null=>false end end end require 'celluloid' require 'sequel' require 'sqlite3' require 'logger' logger = Logger.new(STDERR) logger.level = Logger::DEBUG DB = Sequel.connect(:adapter=>'sqlite', :database=>'/tmp/test.db') DB.loggers << logger Celluloid.logger = logger DB[:artists].delete class Reader include Celluloid include Celluloid::Logger def initialize every(1){ run_sql } end def run_sql artists = DB[:artists] bfr = Time.new begin artists.where( :id => 1 ).first est = Time.new - bfr debug "--> read #{est}sec #{Actor.current}" rescue est = Time.new - bfr debug "--> read(#{$!}) #{est}sec #{Actor.current}" STDERR.puts $!.backtrace end end end class Writer include Celluloid include Celluloid::Logger def initialize every(1){ run_sql } end def run_sql artists = DB[:artists] name = sprintf("test%f",Time.new.to_f) bfr = Time.new begin DB.transaction do artists.insert( :name => name ) est = Time.new - bfr debug "--> write #{est}sec #{Actor.current}" end rescue est = Time.new - bfr debug "--> write(#{$!}) #{est}sec #{Actor.current}" STDERR.puts $!.backtrace end end end manager = Celluloid::SupervisionGroup.run! manager.pool(Writer, as: :writer, args: [], size: 2) manager.pool(Reader, as: :reader, args: [], size: 2) sleep I, [2015-04-04T12:49:34.936240 #45666] INFO -- : (0.000677s) PRAGMA foreign_keys = 1 I, [2015-04-04T12:49:34.936358 #45666] INFO -- : (0.000028s) PRAGMA case_sensitive_like = 1 I, [2015-04-04T12:49:34.936914 #45666] INFO -- : (0.000485s) SELECT sqlite_version() I, [2015-04-04T12:49:34.938964 #45666] INFO -- : (0.001836s) DELETE FROM `artists` WHERE (1 = 1) I, [2015-04-04T12:49:35.944744 #45666] INFO -- : (0.000109s) BEGIN I, [2015-04-04T12:49:35.947201 #45666] INFO -- : (0.001993s) INSERT INTO `artists` (`name`) VALUES ('test1428119375.944484') D, [2015-04-04T12:49:35.947593 #45666] DEBUG -- : --> write 0.002763sec #<Writer:0x007fdc14512d20> I, [2015-04-04T12:49:35.948436 #45666] INFO -- : (0.000281s) PRAGMA foreign_keys = 1 I, [2015-04-04T12:49:35.950077 #45666] INFO -- : (0.001104s) COMMIT I, [2015-04-04T12:49:35.950211 #45666] INFO -- : (0.000039s) PRAGMA case_sensitive_like = 1 I, [2015-04-04T12:49:35.950584 #45666] INFO -- : (0.000125s) BEGIN I, [2015-04-04T12:49:35.951233 #45666] INFO -- : (0.000234s) PRAGMA foreign_keys = 1 I, [2015-04-04T12:49:35.951347 #45666] INFO -- : (0.000047s) PRAGMA case_sensitive_like = 1 I, [2015-04-04T12:49:35.951766 #45666] INFO -- : (0.000309s) SELECT * FROM `artists` WHERE (`id` = 1) LIMIT 1 I, [2015-04-04T12:49:35.952320 #45666] INFO -- : (0.000332s) INSERT INTO `artists` (`name`) VALUES ('test1428119375.947833') D, [2015-04-04T12:49:35.952678 #45666] DEBUG -- : --> write 0.004531sec #<Writer:0x007fdc1302b308> I, [2015-04-04T12:49:35.953675 #45666] INFO -- : (0.000919s) COMMIT I, [2015-04-04T12:49:35.953958 #45666] INFO -- : (0.000180s) SELECT * FROM `artists` WHERE (`id` = 1) LIMIT 1 D, [2015-04-04T12:49:35.954264 #45666] DEBUG -- : --> read 0.005512sec #<Reader:0x007fdc14508988> D, [2015-04-04T12:49:35.954528 #45666] DEBUG -- : --> read 0.005523sec #<Reader:0x007fdc14502600> I, [2015-04-04T12:49:36.947791 #45666] INFO -- : (0.000094s) BEGIN I, [2015-04-04T12:49:36.948589 #45666] INFO -- : (0.000563s) INSERT INTO `artists` (`name`) VALUES ('test1428119376.947594') I, [2015-04-04T12:49:36.949251 #45666] INFO -- : (0.000052s) BEGIN I, [2015-04-04T12:49:41.972344 #45666] INFO -- : (0.000161s) INSERT INTO `artists` (`name`) VALUES ('test1428119381.971985') D, [2015-04-04T12:49:41.972688 #45666] DEBUG -- : --> write 0.001076sec #<Writer:0x007fdc14512d20> I, [2015-04-04T12:49:41.975270 #45666] INFO -- : (0.002452s) COMMIT D, [2015-04-04T12:49:41.975640 #45666] DEBUG -- : --> write 0.000445sec #<Writer:0x007fdc14512d20> I, [2015-04-04T12:49:41.976282 #45666] INFO -- : (0.000225s) SELECT * FROM `artists` WHERE (`id` = 1) LIMIT 1 D, [2015-04-04T12:49:41.976555 #45666] DEBUG -- : --> read 0.000424sec #<Reader:0x007fdc14502600> E, [2015-04-04T12:49:41.976701 #45666] ERROR -- : SQLite3::BusyException: database is locked: INSERT INTO `artists` (`name`) VALUES ('test1428119376.949136') I, [2015-04-04T12:49:41.977171 #45666] INFO -- : (0.000044s) ROLLBACK I, [2015-04-04T12:49:41.977499 #45666] INFO -- : (0.000037s) BEGIN I, [2015-04-04T12:49:41.977973 #45666] INFO -- : (0.000315s) INSERT INTO `artists` (`name`) VALUES ('test1428119381.977403') D, [2015-04-04T12:49:41.978288 #45666] DEBUG -- : --> write(SQLite3::BusyException: database is locked) 5.028121sec #<Writer:0x007fdc1302b308> /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/statement.rb:108:in `step' I, [2015-04-04T12:49:41.978742 #45666] INFO -- : (0.000142s) SELECT * FROM `artists` WHERE (`id` = 1) LIMIT 1 D, [2015-04-04T12:49:41.979018 #45666] DEBUG -- : --> read 0.000308sec #<Reader:0x007fdc14508988> /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/statement.rb:108:in `block in each' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/statement.rb:107:in `loop' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/statement.rb:107:in `each' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:158:in `to_a' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:158:in `block in execute' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:95:in `prepare' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:134:in `execute' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/sqlite.rb:187:in `block (2 levels) in _execute' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/logging.rb:37:in `log_yield' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/sqlite.rb:187:in `block in _execute' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `block in synchronize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/connection_pool/threaded.rb:85:in `hold' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `synchronize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/sqlite.rb:178:in `_execute' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/adapters/sqlite.rb:149:in `execute_insert' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:927:in `execute_insert' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/dataset/actions.rb:336:in `insert' /tmp/test.rb:53:in `block in run_sql' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/transactions.rb:134:in `_transaction' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/transactions.rb:108:in `block in transaction' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `block in synchronize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/connection_pool/threaded.rb:98:in `hold' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/connecting.rb:250:in `synchronize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/sequel-4.20.0/lib/sequel/database/transactions.rb:97:in `transaction' /tmp/test.rb:52:in `run_sql' /tmp/test.rb:44:in `block in initialize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/celluloid-0.16.0/lib/celluloid/actor.rb:357:in `block in task' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/celluloid-0.16.0/lib/celluloid/tasks.rb:57:in `block in initialize' /Users/hiroysato/.rbenv/versions/2.2.1/lib/ruby/gems/2.2.0/gems/celluloid-0.16.0/lib/celluloid/tasks/task_fiber.rb:15:in `block in create' -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
