小鱼儿玄机30码姐妹实现表数据的增修删时,2.对

2019-07-20 作者:数据库   |   浏览(89)

一.事务的概述

   上一章节里,重点讲到了锁,以及锁与事务的关系。离上篇发布时间好几天了,每天利用一点空闲时间还真是要坚持。听《明朝那些事儿》中讲到"人与人最小的差距是聪明,人与人最大的差距是坚持"很经典的一句话一直记得。这篇重点围绕事务来开展。涉及的知识点包括:事务的概述,事务并发控制模型,并发产生的负面影响,事务隔离级别以及不同的表现。本章多以文字描述为主,没有多少代码量,重点是阐述不同隔离级别的不同表现,在以后的业务中,涉及到事务时,本文可以用来做个参考。

1.1 事务ACID

    事务作为一个逻辑工作单元执行一系列的操作,它包括四个属性:原子性、一致性、隔离性和持久性 (ACID) 属性, 只有这样才能成为一个事务。  

    原子性:当一个事务被当作一个单独的工作单元时,不管事务内有什么,都是一个整体。对于其数据修改,要么全都执行,要么全都不执行。  

       一致性:事务在完成时,必须使所有的数据都保持一个逻辑一致状态。   

  隔离性:并发事务所做的修改必须与其他并发事务所做的修改隔离。 事务能识别数据所处的状态,要么是另一并发事务修改它之前的状态,要么是并发事务修改它之后的状态。

  持久性:一但事务完全,它的效果是永久存于系统的。该修改即使出现系统故障也将一直保持。 SQL Server 2014和更高版本启用延迟的持久事务。

1.2 事务的操作模式有几下几种:

  自动提交事务:每条单独的语句都是一个事务。

  显式事务:每个事务均以 BEGIN TRANSACTION 语句显式开始,以 COMMIT 或 ROLLBACK 语句显式结束。

  隐式事务:在前一个事务完成时新事务隐式启动,但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成。

  批处理级事务:只能应用于多个活动结果集 (MARS),在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。在sql server 2000 必须对每个 SqlCommand 对象使用独立的 SqlConnection 对象。但是 SQL Server 2005 启用了 MARS,可以共用一个SqlConnection 对象。

       本章重点讲到显式事务的隔离级别

SQL Server “复制”表结构,创建_小鱼儿玄机30码姐妹,Log表及触发器

实例效果:

实现表数据的增修删时,记录日志。

1.“复制”现有表,

   创建相应的_Log表;

 (注意点:

通过select union all 的方式,避免了IDENTITY 的“复制”,
即如果原表有 PK 如 ID Identity,_Log表 仅“复制”ID int,“不复制” Identity属性,
以便 Insert Update Delete时,可以Insert到Log表。)

2.对现有表,创建Insert,Update,Delete的触发器,

  并将相应数据 记录到对应的_Log表

BEGIN TRAN   
BEGIN TRY  


--定义TAB_CURSOR
DECLARE TAB_CURSOR CURSOR read_only
FOR
   SELECT name FROM SysObjects Where XType='U' 
  -- AND name = N'T01ConstItem' 
  and [name] <> N'dtproperties'
   ORDER BY Name;

--打开
OPEN TAB_CURSOR

DECLARE @P_TabName NVARCHAR(200);
DECLARE @P_TabName_Log NVARCHAR(200);
DECLARE @P_Create_Log_Tab NVARCHAR(4000);
DECLARE @P_Create_Trig_I NVARCHAR(4000);
DECLARE @P_Create_Trig_U NVARCHAR(4000);
DECLARE @P_Create_Trig_D NVARCHAR(4000);

FETCH NEXT FROM TAB_CURSOR 
           INTO @P_TabName
--循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
    BEGIN   
    SET @P_TabName_Log = CONCAT(@P_TabName,N'_Log');

    SET @P_Create_Log_Tab = N' SELECT * ';
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' INTO ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName_Log );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' UNION ALL ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' SELECT TOP (1) * ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab, N' WHERE 1=0 ; ');

    EXEC( @P_Create_Log_Tab);

    --SET @P_Create_Log_Tab = CONCAT(N' SET IDENTITY_INSERT ',@P_TabName_Log ,' ON '); 
    --EXEC( @P_Create_Log_Tab);


    SET @P_Create_Trig_I = N' create trigger ';
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' trig_',@P_TabName,N'_I ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' on ',@P_TabName,N' after INSERT as ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N' begin ');    
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' select * , N''I'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'end ');

    --select @P_Create_Trig_I;

    EXEC( @P_Create_Trig_I);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_U ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after UPDATE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UD'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UI'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_D ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after DELETE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''D'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    END
   FETCH NEXT FROM TAB_CURSOR INTO @P_TabName
END

--关闭
CLOSE TAB_CURSOR
--释放
DEALLOCATE TAB_CURSOR

COMMIT TRAN;  

END TRY  
BEGIN CATCH  
    SELECT ERROR_MESSAGE() AS ErrorMessage  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ROLLBACK TRAN;  
END CATCH

 

转载来源:

原理:

实例效果:

二. 事务并发模型

  2.1 并发访问是指:多用户同时访问一种资源被视为并发访问资源。 并发数据访问需要某些机制,以防止多个用户试图修改其他用户正在使用的资源时产生负面影响,机制就是下面讲的事务隔离级别。处于活动状态而不互相干涉的并发用户数据越多,并发性就越好。当一个正在修改数据的用户阻止了其他用户读取数据,或者当一个正在读取数据的用户阻止了其它用户修改数据时,并发性就降低了。

  2.2 并发类型

    在sqlserver里数据库系统可以采用两种方式来管理并发数据访问:乐观并发控制和悲观并发控制,在sql server 2000以前只有悲观并发。乐观并发控制是一种称为行版本控制(row versioning)的技术支持。这二种技术并发控制的区别在于:是在冲突发生前进行防止,还是在发生后采用某种方法来处理冲突。

  悲观并发控制

      在悲观并发中,sql server是获取锁来阻塞对于其它用户正在使用数据的访问。  用户操作的读与写之间是会互相阻塞的。

       乐观并发控制

    乐观并发控制默认采用行版本控制使其它用户能够看到修改操作发生以前的数据状态,旧版本数据行会保存下来。因些读取数据不会受到其它用户正对该数据进行修改操作的影响,换言之修改数据不会受到其它用户正对该数据进行读取影响。 因为读取用户访问的数据行是一个被保存过的版本。  用户读与写之间不会互相阻塞,但写与写还是会发生阻塞。

  2.3  事务并发带来的负面影响

       修改数据的用户会影响同时读取或修改相同数据的其他用户。 即这些用户可以并发访问数据。 如果数据存储系统没有并发控制,则用户可能会看到以下负面影响:

并发影响 

定义

丢失更新                                                            

       当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,会发生丢失更新问题。 每个事务都不知道其他事务的存在。   最后的更新 将覆盖由其他事务所做的更新,这将导致数据丢失。 

脏读

 当一个用户修改了数据但尚未提交修改,而另一个正在读取的用户会读到这个修改从而导致不一致的状态发生。

不可重复读

一个用户在同一个事务中分别以两个读操作间隔读取相同资源时可能会得到不同的值。

虚拟读取(幻影)

一个事务里执行两个相同的查询,但第二个查询返回的行集合是不同的,此时就会发生虚拟读取。这种情况发生在where 查询中,比如 where count(1)<10。  同一个事务中多次使用相同的条件查询,select操作返回不同数据的结果集。

Mysql热备份有三个步骤:

实现表数据的增修删时,记录日志。

三.事务隔离级别

  在sql server 2005及以上 支持五种隔离级别来控制“读”操作的行为,其中有三个是悲观并发模式,一个是乐观并发模式,剩下一个存在两种模式。 下面介绍隔离级别从允许的并发负作用(例如脏读或虚拟读取)的角度进行描述。

隔离级别

 定义

未提交读
READUNCOMMITTED 

 隔离事务的最低级别,未提交读不会发出共享锁,允许脏读,一个事务可能看见其他事务所做的尚未提交的更改。未提交读不会发出共享锁. 该项的作用与与SELECT表上加NOLOCK相同。

 

已提交读
READ COMMITTED

 一个事务不能读取其它事务修改但未提交的数据,避免了脏读。事务内语句运行完后便会释放共享锁,而不是等到事务提交的时候。 这是数据库引擎默认级别。

可重复读
REPEATABLE READ

 事务内查询语句运行完后不会释放共享锁,而是等到事务提交后.其它事务不能修改,删除,但可以插入新数据。
 因为不是范围锁,可能发生虚拟读取

 可序列化SERIALIZABLE

 隔离事务的最高级别,事务之间完全隔离。 阻止其它事务删除或插入任何行。 相当于SELECT上加HOLDLOCK相同, SELECT 操作使用 WHERE 子句时获取范围锁,主要为了避免虚拟读取

已提交读 快照隔离
READ COMMITTED SNAPSHOT ISOLATION level (RCSI)

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时,已提交读隔离使用行版本控制提供语句级读取一致性。 读取操作只需要 SCH-S 表级别的锁,不需要页锁或行锁。 使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。 

快照隔离
SNAPSHOT ISOLATION level
(SI)

 快照隔离级别使用行版本控制来提供事务级别的读取一致性。 读取操作不获取页锁或行锁,只获取 SCH-S 表锁。 读取其他事务修改的行时,读取操作将检索启动事务时存在的行的版本。 当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时,只能对数据库使用快照隔离。 默认情况下,用户数据库的此选项设置为 OFF。

  sql server主要是通过共享锁申请和释放机制的不同处理,来实现不同的事务隔离级别。不同隔离级别允许的并发副作用如下:

隔离级别 脏读 不可重复读 幻影读 并发控制模型
 未提交读 悲观
 已提交读 悲观
 已提交读快照 乐观
 可重复读 悲观
 快照 乐观
可串行化 悲观

  不同隔离级别对共享锁的不同处理方式如下:

隔离级别 是否申请共享锁 何时释放 有无范围锁
未提交读  
已提交读 当前语句做完时
可重复读 事务提交时
可序列化 事务提交时
  1. 作为主服务器的Master,会把数据库每一次的变动(每条sql语句)都记录到二进制日志Binarylog中。
  2. 作为从服务器Slave, 使用master上的用户登陆到 master上,读取master的Binarylog,写入到自己的中继日志 Relaylog。
  3. 然后从服务器自己的sql线程会负责读取这个中继日志,并执行一遍

1.“复制”现有表,

本文由小鱼儿玄机30码发布于数据库,转载请注明出处:小鱼儿玄机30码姐妹实现表数据的增修删时,2.对

关键词: 小鱼儿玄机30码