SQLite supports IMMEDIATE and EXCLUSIVE transactions which prevents SQLITE_BUSY errors at the expense of concurrent performance.
DEFERRED transactions remain the default, but may be explicitly specified in case this default behavior changes. ref: http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#Transaction_and_Database_Locking ref: https://www.sqlite.org/lang_transaction.html --- I chose ":mode" for the name of this parameter to be consistent with what appears in the SQLite3::Database#transaction code/rdoc. Perhaps being able to force a transaction mode on connect like the Perl DBD::SQLite module allows would be a good feature: my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", { sqlite_use_immediate_transaction => 1, }); A Sequel equivalent could be: Sequel.connect("sqlite:///path/to/db", :transaction_mode => :immediate) This would make it easier to write database-independent code. I've pushed this patch to my "master" on git://bogomips.org/sequel.git $ git pull git://bogomips.org/sequel.git master (commit 49ee878ed0808f609fb836f0b2341357166ca605) lib/sequel/adapters/shared/sqlite.rb | 11 +++++++++++ spec/adapters/sqlite_spec.rb | 20 +++++++++++++++++++- 2 files changed, 30 insertions(+), 1 deletion(-) diff --git a/lib/sequel/adapters/shared/sqlite.rb b/lib/sequel/adapters/shared/sqlite.rb index b4ae96b..64a7d3f 100644 --- a/lib/sequel/adapters/shared/sqlite.rb +++ b/lib/sequel/adapters/shared/sqlite.rb @@ -10,6 +10,11 @@ module DatabaseMethods TABLES_FILTER = "type = 'table' AND NOT name = 'sqlite_sequence'".freeze TEMP_STORE = [:default, :file, :memory].freeze VIEWS_FILTER = "type = 'view'".freeze + TRANSACTION_MODE = { + :deferred => "BEGIN DEFERRED TRANSACTION".freeze, + :immediate => "BEGIN IMMEDIATE TRANSACTION".freeze, + :exclusive => "BEGIN EXCLUSIVE TRANSACTION".freeze, + }.freeze # Whether to use integers for booleans in the database. SQLite recommends # booleans be stored as integers, but historically Sequel has used 't'/'f'. @@ -244,6 +249,12 @@ def alter_table_sql(table, op) end end + def begin_new_transaction(conn, opts) + sql = TRANSACTION_MODE[opts[:mode]] || begin_transaction_sql + log_connection_execute(conn, sql) + set_transaction_isolation(conn, opts) + end + # A name to use for the backup table def backup_table_name(table, opts={}) table = table.gsub('`', '') diff --git a/spec/adapters/sqlite_spec.rb b/spec/adapters/sqlite_spec.rb index 237e746..c9b3239 100644 --- a/spec/adapters/sqlite_spec.rb +++ b/spec/adapters/sqlite_spec.rb @@ -595,4 +595,22 @@ @db.drop_column :a, :b @db.indexes(:a).should == {:a_a_index=>{:unique=>false, :columns=>[:a]}} end -end + + specify "should have support for various #transaction modes" do + sqls = [] + @db.loggers << (l=Class.new{%w'info error'.each{|m| define_method(m){|sql| sqls << sql}}}.new) + + @db.transaction(:mode => :immediate) do + sqls.last.should == "BEGIN IMMEDIATE TRANSACTION" + end + @db.transaction(:mode => :exclusive) do + sqls.last.should == "BEGIN EXCLUSIVE TRANSACTION" + end + @db.transaction(:mode => :deferred) do + sqls.last.should == "BEGIN DEFERRED TRANSACTION" + end + @db.transaction do + sqls.last.should == Sequel::Database::SQL_BEGIN + end + end +end -- Eric Wong -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
