Atomic searching and creating entities on demand with SQL/Doctrine2

I want to associate bookmarks with tags. Tags may not exist yet.

-- see source after questions for complete schema
Bookmark:     _id_|…
Tag:          _id_|_title_|…
bookmark_tag: _bookmark_id|_tag_id_

Current solution:

if tag exists : load tag (id) from db
else          : store tag to db // non-atomic operation => duplicates possible

link tag with bookmark

For every tag the database is requested to search for it and return the location (id). I am not sure if this is the best solution, but it works.

More important is the non-atomic operation done, when storing a new bookmark. Tags have a unique title, so storing a duplicate will result in an Exception (PDOException), which will close Doctrine's EntitiyManager.

While searching for the tag and storing it, another process could create the tag, so the first process will break while creating a duplicated entity.

I am using Symfony2 with Doctrine2. DB-Backend is MySQL, but i hope for a generic (Doctrine2) solution.

Questions

How would you solve this dilemma? Do you know a better solution?

Is Doctrine2 able to create missing Tags on demand and use existings ones when addTag(Tag $tag)-ing them to Bookmark?

Source/BookmarkController.php

/**
 * Creates a new Bookmark entity.
 *
 * @Route("/create", name="bookmarks_create")
 * @Method("post")
 * @Template("XBookmarksBundle:Bookmark:new.html.twig")
 */
public function createAction()
{
    $entity  = new Bookmark();
    $request = $this->getRequest();
    $form    = $this->createForm(new BookmarkType(), $entity);
    $form->bindRequest($request);

    if ($form->isValid()) {
        $em = $this->getDoctrine()->getEntityManager();

        // static tag for testing
        $tagTitle = 'Lorem Ipsum';

        $result = $this->getDoctrine()->getRepository('XBookmarksBundle:Tag')
            ->findBy(array('title' => $tagTitle));

        // found tag => use it
        if (count($result) > 0) {
            $tag = $result[0];
        }

        // create new tag
        else {
            // FATAL not atomic => tag could exists now

            $tag = new Tag();
            $tag->setTitle($tagTitle);
        }

        // add tag to bookmark
        $entity->addTag($tag);

        $em->persist($entity);
        $em->flush();

        return $this->redirect($this->generateUrl('bookmarks开发者_运维知识库_show', array('id' => $entity->getId())));
    }

Source/Bookmark.php

/**
 * X\BookmarksBundle\Entity\Bookmark
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="X\BookmarksBundle\Entity\BookmarkRepository")
 */
class Bookmark
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string $title
     *
     * @ORM\Column(name="title", type="string", length=255)
     */
    private $title;

    /**
     * @var string $uri
     *
     * @ORM\Column(name="uri", type="string", length=255)
     */
    private $uri;

    /**
     * @var datetime $created_at
     *
     * @ORM\Column(name="created_at", type="datetime")
     */
    private $created_at;

    /**
     * @var datetime $deleted_at
     *
     * @ORM\Column(name="deleted_at", type="datetime")
     */
    private $deleted_at;


    /**
     * @ORM\ManyToMany(targetEntity="Tag", cascade={"persist", "remove"}) */
    private $tags;



    public function __construct()
    {
        $this->tags = new ArrayCollection();
    }

    public function getTags () {
            return $this->tags;
    }

    public function addTag (Tag $tag) {
        $this->tags->add($tag);
    }

    public function setTags ($tags) {
        // TODO
    }

    /* DEFAULT SETTERS AND GETTERS FOR OTHER ATTRIBUTES */
}

Source/Tag.php

/**
 * X\BookmarksBundle\Entity\Tag
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="X\BookmarksBundle\Entity\TagRepository")
 */
class Tag
{
    /**
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string $title
     *
     * @ORM\Column(name="title", type="string", length=64, unique=true)
     * -- ORM\Id
     * -- ORM\GeneratedValue(strategy="NONE")
     */
    private $title;

    /**
     * @var datetime $created_at
     *
     * @ORM\Column(name="created_at", type="datetime")
     */
    private $created_at;

    /**
     * @var datetime $deleted_at
     *
     * @ORM\Column(name="deleted_at", type="datetime", nullable=true)
     */
    private $deleted_at;


    public function __construct () {
        $this->created_at = new \DateTime('now');
        $this->id=42;
        $this->title="le fu";
    }
}

You could do it like this:

// ...
// create new tag
else {
    try {
        $tag = new Tag();
        $tag->setTitle($tagTitle);
        $em->persist($tag);
        $em->flush();
    } catch (PDOException $e) {
        // also you could check for exception code here
        $result = $this->getDoctrine()->getRepository('XBookmarksBundle:Tag')
            ->findBy(array('title' => $tagTitle));

        // found tag
        if (count($result) > 0) {
            $tag = $result[0];
        } else {
            throw new Exception("Something went wrong");
        }

    }
}
// ...