3

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?

Richie Cotton
  • 118,240
  • 47
  • 247
  • 360
Statwonk
  • 713
  • 1
  • 8
  • 21

1 Answers1

2

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)
Victor K.
  • 4,054
  • 3
  • 25
  • 38