title: Magento: Custom MySQL query with locks not working
body: <p>I'm trying to write a function to SELECT the least-recently fetched value from a table in my database.  I do this by SELECTing a row and then immediately changing the <code>last_used</code> field.</p>
  
  <p>Because this involves a SELECT and UPDATE, I'm trying to do this with locks.  The locks are to ensure that concurrent executions of this query won't operate on the same row.</p>
  
  <p>The query runs perfectly fine in phpMyAdmin, but fails in Magento.  I get the following error:</p>
  
  <p><code>SQLSTATE[HY000]: General error</code></p>
  
  <p>Error occurs here:</p>
  
  <pre><code>#0 /var/www/virtual/magentodev.com/htdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(249): PDOStatement-&gt;fetch(2)
  </code></pre>
  
  <p>Here is my model's code, including the SQL query:</p>
  
  <pre><code>$write = Mage::getSingleton('core/resource')-&gt;getConnection('core_write');
  
  $sql = "LOCK TABLES mytable AS mytable_write WRITE, mytable AS mytable_read READ;
          SELECT @val := unique_field_to_grab FROM mytable AS mytable_read ORDER BY last_used ASC LIMIT 1;        
          UPDATE mytable AS mytable_write SET last_used = unix_timestamp() WHERE unique_field_to_grab = @val LIMIT 1;
          UNLOCK TABLES;
          SELECT @val AS val;";
  
  $result = $write-&gt;raw_fetchrow($sql, 'val');
  </code></pre>
  
  <p>I've also tried using <code>raw_query</code> and <code>query</code> instead of <code>raw_fetchrow</code> with no luck.</p>
  
  <p>Any thoughts on why this doesn't work?  Or is there a better way to accomplish this?</p>
  
  <p><strong>EDIT</strong>: I'm starting to think this may be related to the PDO driver, which Magento is definitely using.  I think phpMyAdmin is using mysqli, but I can't confirm that.</p>