使用CodeIgniter插入时重复

Question

I have two tables - a post table Posts and a post collection table Collections. When a user creates a post, I will insert it to the database and update the corresponding collection (for sorting-based-on-update-time purpose). To avoid duplicate posts (by either user operation or network churn), I will check if a same post (based on content and author) exists. However, I still get duplicate posts (with exactly same create time and no user duplicate operations) occasionally. What could be the cause - CodeIgniter, transaction, or weird network status? Since I check existing same posts explicitly, I guess the duplication is caused by the code between trans_start() and trans_complete().

    # check if a same post exists already within 1 hour
    $sql="SELECT id FROM Posts WHERE content=? AND author_id=(SELECT id FROM Users WHERE username=? LIMIT 1) AND create_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR) LIMIT 1";
    $query=$this->db->query($sql, array($content, $username));
    if($query->num_rows()>=1){
        return 0;
    }
    # start post        
    $this->db->trans_start();
    $sql="INSERT INTO Posts (content, author_id) VALUES (?, (SELECT id FROM Users WHERE username=? LIMIT 1))";
    $this->db->query($sql, array($content, $username));
    if($this->db->affected_rows()>0){
        # update post collection update_time
        $insert_id=$this->db->insert_id();
        $sql="UPDATE Collections SET update_time=now() WHERE id=$collection_id";
        $query=$this->db->query($sql);
        if($this->db->affected_rows()>0){
            $ret=$insert_id;
        }else{
            $ret=0;
        }
    }else{
        $ret=0;
    }
    $this->db->trans_complete();
    return $ret;

Answer 1

A temporary workaround is to add a unique index on the (content, author_id, create_time) columns, enabling a check on the mysql level. But still, I don't know why the duplication appears...