DbUpdateConcurrencyException 使用 Entity Framework 6 和 MySql

2022-01-20 00:00:00 concurrency mysql entity-framework entity-framework-6

我在使用 EF6 和 MySQL 进行并发检查时遇到问题.

I'm having trouble with concurrency checks using EF6 and MySQL.

我遇到的问题是当我尝试将数据保存到数据库时引发并发异常.如果您检查输出到控制台的 sql，它会尝试使用 where 子句中的旧值从数据库中查询并发字段.因为这个字段已经被数据库更新了.

The problem I'm having is that I get a concurrency exception thrown when I try to save data to the database. If you examine the sql that is output to the console it tries to query the concurrency field from the database using the old value in the where clause. Because this field has been updated by the database.

环境:

  • Windows 7 64 位
  • Visual Studio 2013

已安装 Nuget 包:

Nuget packages installed:

  • EF 6.0.1
  • MySql.ConnectorNET.Data 6.8.3.2
  • MySql.ConnectorNET.Entity 6.8.3.2

演示数据库 SQL:

DROP DATABASE IF EXISTS `bugreport`;
CREATE DATABASE IF NOT EXISTS `bugreport`;
USE `bugreport`;

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `TestId` int(10) NOT NULL AUTO_INCREMENT,
  `AStringField` varchar(50) DEFAULT NULL,
  `DateModified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TestId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `test` (`TestId`, `AStringField`, `DateModified`) VALUES
    (1, 'Initial Value', '2014-07-11 09:15:52');

演示代码:

using System;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace BugReport
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new BugReportModel())
            {
                context.Database.Log = (s => Console.WriteLine(s));

                var firstTest = context.tests.First();
                firstTest.AStringField = "First Value";

                // Exception is thrown when changes are saved.
                context.SaveChanges();              

                Console.ReadLine();
            } 
        }
    }

    public class BugReportModel : DbContext
    {
        public BugReportModel()
            : base("name=Model1")
        {

        }

        public virtual DbSet<test> tests { get; set; }
    }


    [Table("test")]
    public class test
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int TestId { get; set; }

        [StringLength(50)]
        public string AStringField { get; set; }

        [ConcurrencyCheck()]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        [Column(TypeName = "timestamp")]
        public System.DateTime DateModified { get; set; }
    }
}

更新:向 MySql 提交了 bug.

Update: Filed bug with MySql.

推荐答案

您应该尝试使用 DB Timestamp/Rowversion 功能.在 EF 中，您声明一个 ByteArray 并将其指定为并发检查字段.DB 设定创造的价值.所有后续更新都可以检查值没有改变DB 根据需要更新 rowversion.这种方法适用于 SQL 服务器.它在 MYSql 上的行为应该相同.

You should be trying to use the DB Timestamp / Rowversion feature. In EF you declare a ByteArray and nominate it as the Concurrency check field. DB sets the value on creation. All subsequent updates can check the value hasnt changed DB updates rowversion as appropriate. This approach works on SQL server. It should behave the same way on MYSql. 

    public  abstract class BaseObject  {
    [Key]
    [Required]
    public virtual int Id { set; get; }

    [ConcurrencyCheck()]
    public virtual byte[] RowVersion { get; set; }

    }

如果你愿意，也可以通过 fluent//首要的关键this.HasKey(t => t.Id);

or via fluent if you like // Primary Key this.HasKey(t => t.Id);

        // Properties
        //Id is an int allocated by DB , with string keys, no db generation now
        this.Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); // default to db generated

        this.Property(t => t.RowVersion)
            .IsRequired()
            .IsFixedLength()
            .HasMaxLength(8)
            .IsRowVersion(); //<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

关于乐观并发模式的文档

相关文章