开发者

INSERT and UPDATE the same row in the same TRANSACTION? (MySQL)

So here's my problem:

I have an article submission form with an optional image upload field.

When the user submits the form - this is roughly what happens:

if($this->view->form->isValid($_POST){

$db->beginTransaction();
try{
    // save content of POST to Article table 
    if(!$this->开发者_如何学JAVA;_saveArticle($_POST)){
         return;
        }

    // resize and save image using ID generated by previous condition
    if(!$this->_saveImage($_FILES){            
        $db->rollback();
        return;
        }

    // update record if image successfully generated
    if(!$this->_updateArticle(){
        $db->rollback();
        }
    $db->commit();
    }
}catch (Exception $e){
    $db->rollback()
}

All Models are saved using mappers, which automate "UPSERT" functionality by checking for the existence of a surrogate key

public function save($Model){
   if(!is_null($Model->id_article){
       $Mapper->insert($Model->getFields());
       return;
    }
    $Mapper->update($Model->getFields(),$Model->getIdentity());
}

The article table has a composite UNIQUE index of ID,Title and URL. In addition, I'm generating a UID that gets added to the ID field of the Model prior to insert (instead of auto-incrementing)

When I try to execute this, it runs fine for the first article inserted into the table - but subsequent calls (with radically different input) triggers a DUPLICATE KEY error. MySQL throws back the ID generated in condition 1 (_saveArticle) and complains that the key already exists...

I've dumped out the Model fields (and the condition state - i.e. insert | update) and they proceed as expected (pseudo):

inserting!
id = null
title = something 
content = something
image = null

updating!
id = 1234123412341234
title = something
content = something else
image = 1234123412341234.jpg

This row data is not present in the database.

I figure this could be one of a few things:

1: I'm loading a secondary DB adapter on user login, allowing them to interface with several sites from one login - this might be confusing the transaction somehow

2: It's a bug of some description in the Zend transaction implementation (possibly triggered by 1)

3: I need to replace the save() with an INSERT ... ON DUPLICATE

4: I should restructure the submission process, or generate a name for the image that isn't dependent on the UID of the previously inserted row.

Still hunting, but I was wondering if anyone else has encountered this kind of issue or could point me in the direction of a solution

best SWK


OK - just for the record, this is entirely possible. The problem was in my application architecture. I was catching Exceptions in my Mapper classes that were handling persistence - and then querying them to return boolean states and thus interrupt the process. This was in turn breaking the try/catch loop which was preventing the insert/update from working correctly. To summarise - Yes - you CAN insert and update the same row in a single transaction. I've ticked community wiki to cancel rep out

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜