Sending a query with a single transaction

Question

I'm using the DBI package to send queries to a MySQL server. I'd like to assure that these queries are sent as a single transaction in order to avoid table lock.

I use the dbSendQuery function to send queries:

df <- fetch(dbSendQuery(connection,
                  statement = "SELECT *
                               FROM table"),
      n = -1)

The DBI package says little about handling transactions, but what it does have is listed under these functions: dbCommit, dbRollback nor dbCallProc under the header:

Note: The following methods deal with transactions and store procedures.

in the vignette. None seem to relate to sending queries as a single transaction.

How can I make sure I'm sending these queries as a single transaction?

Answer 1

Warning: not tested.

You would need some help from MySQL. By default, MySQL runs with auto commit mode enabled. To disable auto commit mode, you would need to issue a START TRANSACTION statement. I suspect dbCommit and dbRollback simply execute COMMIT and ROLLBACK, respectively.

Details: http://dev.mysql.com/doc/refman/5.0/en/commit.html

So you would need to do something like

dbSendQuery(connection, "START TRANSACTION")
# add your dbSendQuery code here
dbCommit(connection)