How to execute transactions with ruby ​​mysql2

Question

I started using mysql2 gem. I'm trying to figure out a few basic things: one of them is how to explicitly execute transactions (for batch operations, for example, multiple INSERT / UPDATE queries).

In old ruby-mysql this was my approach:

 client = Mysql.real_connect(...) inserts = [ "INSERT INTO ...", "UPDATE .. WHERE id=..", # etc ] client.autocommit(false) inserts.each do |ins| begin client.query(ins) rescue # handle errors or abort entirely end end client.commit 

I could not find much in the docs - how can I do the same with mysql2 ?

Answer 1

I just completed the implementation:

 class DBConnector def transaction(&block) raise ArgumentError, "No block was given" unless block_given? begin client.query("BEGIN") yield client.query("COMMIT") rescue client.query("ROLLBACK") end end end 

So you can use like this:

 DBConnector.transaction do # your db queries here end 

Answer 2

This question aroused my curiosity, so I figured out how Ruby on Rails handles transactions, and I found this code :

 def begin_db_transaction execute "BEGIN" rescue Exception # Transactions aren't supported end def commit_db_transaction #:nodoc: execute "COMMIT" rescue Exception # Transactions aren't supported end def rollback_db_transaction #:nodoc: execute "ROLLBACK" rescue Exception # Transactions aren't supported end 

Have you tried executing begin and commit statements around your other statements?

 client.query('begin') inserts.each do |ins| begin client.query(ins) rescue client.query('rollback') return end end client.query('commit') 

Answer 3

Using the Bruno template by adding a transaction status indicator:

 def transaction(&block) raise ArgumentError, "No block was given" unless block_given? begin raw_query("BEGIN") yield raw_query("COMMIT") return true # Successful Transaction rescue raw_query("ROLLBACK") return false # Failed Transaction end end 

C # transaction interaction:

 def run_queries(queries) raise ArgumentError, "Invalid Queries Argument: #{queries}" unless queries.respond_to?(:each) success = transaction do queries.each do |q| raw_query(q) end end raise RuntimeError, "Transaction Failed for Queries: #{queries}" unless success end