Can db query handle start transaction and commit?

Asked by jsherk on 2010-11-25

Using InnoDB tables, if I setup my own class query in the models folder to extend PHPDS_query, can it handle an sql with START TRANSACTION and COMMIT? I need to lock a row in a table to read a value and then update the value. The value read needs to be unique, so I use the FOR UPDATE to lock it, then update it, then commit to unlock.

Will the example below cause any problems or should it function exactly as shown/intended?

Example:
class MyPlugin_doQuery extends PHPDS_query
{
  protected $sql = "
    START TRANSACTION;
    SELECT counter FROM _db_MyPlugin_table WHERE id=1 FOR UPDATE;
    UPDATE _db_MyPlugin_table SET counter = counter + 1 WHERE id=1;
    COMMIT;
  ";

  public function invoke($parameters)
  {
    $example_array = parent::invoke();
    //Do some stuff
    return;
  }
}

Question information

Language:
English Edit question
Status:
Answered
For:
PHPDevShell Edit question
Assignee:
No assignee Edit question
Last query:
2010-11-25
Last reply:
2010-11-25
Greg (gregfr) said : #1

We already use transaction based queries for the whole script, so you don't need to do that. Furthermore, having several queries inside a single database call may not be allowed depending on you MySQL configuration.

jsherk (jeff-forerunnertv) said : #2

So would I just need to remove the Start Transaction and Commit like this?
protected $sql = "
    SELECT counter FROM _db_MyPlugin_table WHERE id=1 FOR UPDATE;
    UPDATE _db_MyPlugin_table SET counter = counter + 1 WHERE id=1;
  ";

Can you help with this problem?

Provide an answer of your own, or ask jsherk for more information if necessary.

To post a message you must log in.