• Introduction
• Handling transactions explicitly with execute()
• Warning – conn begin(), commit() and rollback() sometimes have no effect

Introduction

If your scripts use the conn:merge{} function, you should note that a call to this function does its work in a single transaction. If it fails, the transaction is rolled back and nothing is altered in the database. This behavior can be optionally turned off if you pass in transaction=false as an argument.

The conn:execute{} function is a lower-level interface. It has no automatic support for transactions, but it provides you with more control, since you can explicitly choose whether or not to use transactions.

Handling transactions explicitly with execute() [top]

When using database connection objects conn:execute does not create a transaction envelope like conn:merge, it’s left to the user’s choice on how to implement transaction support.

This is a suggested approach with Microsoft SQL Server:

   conn = db.connect{
      api=db.SQL_SERVER, 
      user='root', 
      password='password', 
      name='test'
   }

   conn:execute{sql = 'BEGIN TRANSACTION',live = true}

   -- Do some stuff.

   if Success then
      conn:execute{sql = 'COMMIT TRANSACTION', live = true}
   else
      conn:execute{sql = 'ROLLBACK TRANSACTION', live = true}
   end

The precise details of the code will depend on what you want to do. Have a look at the retry logic example also.

See also:

• Using Database Connection Objects

Warning – conn begin(), commit() and rollback() sometimes have no effect [top]

If transactions are not supported, then conn:begin{}, conn:commit{} and conn:rollback{} have no effect. This is not regarded as an error condition, so no error is raised in the Translator. When transactions are not supported, then each individual SQL statement is committed separately. For example: A call to conn:execute{} that contains three SQL statements, will be run as three independent commands (one for each SQL statement).

Probably the most commonly used database that does not support transactions is MySQL when it used with the MyISAM database engine (the newer InnoDB engine for MySQL has transaction support).