这个SQL代码并发安全吗？

Question

我非常确定这段代码是正确的。我想知道你们对insertMediaTags函数(第二个函数)的看法。我担心的是下面的并发是安全的吗？如果insertMediaTags足够优化呢？注意，由于first func，它在一个事务中，但它也在一个循环中，这可能意味着它是坏的？

我对你们可能有的任何编码实践，风格或建议持开放态度。(我知道有人会问，我使用的是sqlite自动柜员机，但其原型代码用于mysql或ms sql的某个版本或其他版本)

    {
        long mediaId;
        //all the credentials should be verified by this point.
        command.CommandText = "SELECT mediaId FROM media " +
            "WHERE userId=@userId AND title=@title;";
        command.Parameters.Add("@userId", DbType.Int64).Value = m.userid;
        command.Parameters.Add("@title", DbType.String).Value = m.title;
        if (command.ExecuteScalar() != null)
            throw new System.InvalidOperationException("Title already exisit");

        using (var dbTrans = connection.BeginTransaction())
        {
            command.CommandText =
                "INSERT INTO " +
                "media ( userid,  catagory,  creation_date,  current_media_date,  current_desc_date,  licence,  title,  desc,  ext) " +
                "VALUES(@userid, @catagory, @creation_date, @current_media_date, @current_desc_date, @licence, @title, @desc, @ext); " +
                "SELECT last_insert_rowid() AS RecordID;";

            DateTime currentDate = m.creation_date;
            command.Parameters.Add("@userid", DbType.Int64).Value = m.userid;
            command.Parameters.Add("@catagory", DbType.Int64).Value = m.catagory;
            command.Parameters.Add("@creation_date", DbType.DateTime).Value = m.creation_date;
            command.Parameters.Add("@current_media_date", DbType.DateTime).Value = currentDate;
            command.Parameters.Add("@current_desc_date", DbType.DateTime).Value = currentDate;
            command.Parameters.Add("@licence", DbType.Int64).Value = m.license;
            command.Parameters.Add("@title", DbType.String).Value = m.title;
            command.Parameters.Add("@desc", DbType.String).Value = m.desc;
            command.Parameters.Add("@ext", DbType.Int64).Value = m.ext;

            mediaId = (long)command.ExecuteScalar();
            //m.collaborateWith
            insertInspired(inspireLinks.external, inspireLinks.internalPair, mediaId);
            insertDerived(deriveLinks.external, deriveLinks.internalPair, mediaId);
            insertMediaTags(m.listTagString, mediaId);
            //command.CommandText = "END TRANSACTION;";            command.ExecuteNonQuery();

            updateMediaForWatchers(m.userid, mediaId, m.catagory, currentDate);
            dbTrans.Commit();
        }
        return mediaId;
    }

    void insertMediaTags(List<string> tags, long mediaId)
    {
        foreach(string tag in tags)
        {
            //assure tag exist
            long tagId;
            command.CommandText = "SELECT tagid FROM tag_name WHERE title=@title;";
            command.Parameters.Add("@title", DbType.String).Value = tag;
            object o = command.ExecuteScalar();
            if (o == null)
            {
                command.CommandText =
                    "INSERT INTO tag_name(title) VALUES(@title); " +
                    "SELECT last_insert_rowid() AS RecordID;";
                command.Parameters.Add("@title", DbType.String).Value = tag;
                tagId = (long)command.ExecuteScalar();
            }
            else
                tagId = (long)o;

            command.CommandText =
                "INSERT INTO media_tags(mediaId, tagid) " +
                "VALUES(@mediaId, @tagid);";
            command.Parameters.Add("@mediaId", DbType.Int64).Value = mediaId;
            command.Parameters.Add("@tagid", DbType.Int64).Value = tagId;
            command.ExecuteNonQuery();

            command.CommandText =
                "UPDATE tag_name SET count = count+1 "+
                "WHERE tagid=@tagid";
            command.Parameters.Add("@tagid", DbType.Int64).Value = tagId;
            command.ExecuteNonQuery();
        }
    }

Answer 1

不，它不是并发安全的。在用于确定标记是否存在的SELECT和用于创建标记的INSERT之间存在潜在的竞争条件。假设线程A执行SELECT并发现它不存在，然后线程B在线程A执行插入之前执行相同的操作。线程B也会尝试插入，但会失败。

Answer 2

在SQL Server中，最好使用SCOPE_IDENTITY()函数。除此之外，我看不出有什么问题。