事务与并发

a.事务(存储过程里调用)

begin transaction

rollback transaction

commit transaction

b.锁定

共享锁：能读取不能修改。读取查询产生

更新锁：在修改数据前获得。如果行被修改，升级为排他锁。如果没有修改，降为共享锁。

排他锁：禁止读和写。在insert,update,delete语句的时候产生

c.并发

脏读：当一个事务在更新一行，第二个事务在第一个事务提交之前读取了那行，第一个事务回滚，第二个事务读到的就是脏数据

不可重复读取：当一个事务在更新数据，第二个事务读取了两次数据，一次在更新前，一次在更新后，两次数据不一致

幻读：当一个事务一次获取一组行，第二个事务从相同表中新增或删除了那条数据，第一个事务重新执行发现数据新增或删除了

更新丢失：当两个事务更新了一行，第二个事务为准，第一个事务更新丢失

隔离级别	脏读	不可重复读取	幻读
read uncommitted	是	是	是
read committed	否	是	是
repeatable read	否	否	是
serializable	否	否	否

 d.乐观并发控制和悲观并发控制

悲观锁：有人先取出文件，其他人不能编辑

乐观锁：有人先取出文件，其他人可以编辑，当提交的时候并发控制策略起作用，提示怎么处理

并发策略:后到者胜出更新，完全匹配更新，基于时间戳的更新，变化值更新

如果业务对象的状态需要与从数据库中取出的状态保持一致，悲观并发控制

时间戳:

利用版本号控制

    public class PersonRepository : IPersonRepository 
    {
        private string _connectionString;        
        private string _FindByIdSQL = "SELECT * FROM People WHERE PersonId = @PersonId";
        private string _InsertSQL = "INSERT People (FirstName, LastName, PersonId, Version) VALUES (@FirstName, @LastName, @PersonId, @Version)";
        private string _UpdateSQL = "UPDATE People SET FirstName = @FirstName, LastName = @LastName, Version = @Version + 1 WHERE PersonId = @PersonId AND Version = @Version;";
        
        public PersonRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Add(Person person)
        {
            using (SqlConnection connection =
                      new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _InsertSQL;
                
                command.Parameters.Add(new SqlParameter("@PersonId", person.Id));                
                command.Parameters.Add(new SqlParameter("@Version", person.Version));                
                command.Parameters.Add(new SqlParameter("@FirstName", person.FirstName));                
                command.Parameters.Add(new SqlParameter("@LastName", person.LastName));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }
        
        public void Save(Person person)
        {
            int numberOfRecordsAffected = 0;

            using (SqlConnection connection =
                      new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _UpdateSQL;

                command.Parameters.Add(new SqlParameter("@PersonId", person.Id));
                command.Parameters.Add(new SqlParameter("@Version", person.Version));
                command.Parameters.Add(new SqlParameter("@FirstName", person.FirstName));
                command.Parameters.Add(new SqlParameter("@LastName", person.LastName));

                connection.Open();
                numberOfRecordsAffected = command.ExecuteNonQuery();
            }

            if (numberOfRecordsAffected == 0)
                throw new ApplicationException(@"No changes were made to Person Id (" + person.Id + "), this was due to another process updating the data.");
            else
                person.Version++;
        }
       

        public Person FindBy(Guid Id)
        {
            Person person = default(Person);

            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = _FindByIdSQL;
                command.Parameters.Add(new SqlParameter("@PersonId", Id));
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        person = new Person
                        {
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            Id = new Guid(reader["PersonId"].ToString()),
                            Version = int.Parse(reader["Version"].ToString())
                        };
                    }
                }
            }

            return person;
        }     
    }

    public void Clean_The_Database_Then_Add_A_Test_Person()
        {
            using (SqlConnection connection =
                      new SqlConnection(_dbConnectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "DELETE People";                
                connection.Open();
                command.ExecuteNonQuery();
            }

            IPersonRepository personRepository = new PersonRepository(_dbConnectionString);
            
            Person personToAdd = new Person();
            personToAdd.FirstName = "Lynsey";
            personToAdd.LastName = "Millett";
            personToAdd.Id = _personId;
            personToAdd.Version = 1;

            personRepository.Add(personToAdd);
        }

    public void An_Exception_Will_Be_Thrown_When_Trying_To_Update_A_Modified_Person()
        {
            IPersonRepository personRepository = new PersonRepository(_dbConnectionString);

            Person personToChangeA = personRepository.FindBy(_personId);
            Person personToChangeB = personRepository.FindBy(_personId);

            Assert.AreEqual(personToChangeA.Version, personToChangeB.Version);

            personToChangeA.FirstName = "Doris";
            // Once the person is saved the version number is generated
            personRepository.Save(personToChangeA);

            Assert.AreNotEqual(personToChangeA.Version, personToChangeB.Version); 

            // This person is now stale and has an old version number
            // saving this person will cause an exception to be thrown.
            personToChangeB.FirstName = "Dasiy";            
            personRepository.Save(personToChangeB);
        }

e.业务事务与系统事务

业务事务：离线并发控制模式

unit of work 模式

f.应用服务器并发

进程并发,多线程同步