xml地图|网站地图|网站标签 [设为首页] [加入收藏]
选择合适的数据类型,锁与事务拨云见日
分类:数据库

 

  在锁与事务系列里已经写完了上篇中篇,这次写完下篇。这个系列俺自认为是有条不紊的进行,但感觉锁与事务还是有多很细节没有讲到,温故而知新可以为师矣,也算是一次自我提高总结吧,也谢谢大伙的支持。在上一篇的末尾写了事务隔离级别的不同表现,还没写完,只写到了重复读的不同隔离表现,这篇继续写完序列化,快照的不同隔离表现,事务隔离级别的总结。最后讲下事务的死锁,事务的分布式,事务的并发检查。

数据库使用Table来存储海量的数据,细分Table结构,数据最终存储在Table Column中,因此,在设计Table Schema时,必须慎重选择Table Column的Data Type,数据类型不仅决定了Column能够存储的数据范围和能够进行的操作,而且合适的数据类型还能提高查询和修改数据的性能。数据类型的选择标准既要满足业务和扩展性的需求,又要使行宽最小(行宽是一行中所有column占用的Byte)。最佳实践:使用与Column最大值最接近的数据类型。

Preface

一. 事务隔离不同表现

设置序列化

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

设置行版本控制已提交读

ALTER DATABASE  Test  SET  READ_COMMITTED_SNAPSHOT on; 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

设置快照隔离

ALTER DATABASE Test
SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

1.1 已重复读和序列化与其它事务并发,的区别如下表格: 

可重复读

序列化 其它事务

SET TRANSACTION ISOLATION

LEVEL REPEATABLE READ

SET TRANSACTION ISOLATION

LEVEL SERIALIZABLE

 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

begin tran

select count(*) from product

where memberID=9708

这里显示500条数据,事务还没有结束 

 
   

begin tran

insert into product

values('test2',9708)

其它事务里,想增加一条数据。

如果并发的事务是可重复读,

这条数据可以插入成功。

如果并发的事务是序列化,

这条数据插入是阻塞的。

select count(*) from product

where memberID=9708

在事务里再次查询时,发现显示501条数据

 select count(*) from product

where memberID=9708

在事务再次查询时,还是显示500条数据

 

 commit tran

在一个事务里,对批数据多次读取,符合条件

的行数会不一样。

 commit tran

事务结束

 如果并发是可序列化并且commit,

其它事务新增阻塞消失,插入开始执行。

1.2 已提交读、行版本控制已提交读、快照隔离,与其它事务并发,的区别如下表格: 

已提交读

行版本控制已提交读 快照隔离 其它事务

SET TRANSACTION ISOLATION

LEVEL READ COMMITTED 

ALTER DATABASE Test SET
READ_COMMITTED_SNAPSHOT
ON;

SET TRANSACTION ISOLATION
LEVEL READ COMMITTED

ALTER DATABASE TEST SET
ALLOW_SNAPSHOT_ISOLATION
ON;

SET TRANSACTION ISOLATION
LEVEL SNAPSHOT

 

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

begin tran

select model from product
where sid=9708

得到值为test

 
     

begin tran
update product set
model='test1'
where sid=1

select model from product
where sid=9708

事务里再次查询 阻塞

select model from product
where sid=9708

事务里再次查询值为test, 读到行版本

select model from product
where sid=9708
事务里再次查询值为test,读到行版本


 
 阻塞解除,再次查询返回 test1

再次查询 test1
其它事务提交后,这里读到的是新
(修改后的)数据

再次查询 test

其它事务提交后,这里读取还是旧数据
(行版本数据)

 commit tran
 事务里updaate修改 修改成功  事务里updaate修改 修改成功  事务里updaate修改, 修改失败报错

 

例如,bit 类型只能存储1和0,能够对bit进行逻辑比较(=或<>),不能对进行算术运算(+,-,*,/,%),不要对其进行>或<的比较,虽然bit类型支持,但是,这不 make sense。

 

二. 事务总结

   2.1   事务不同隔离级别的优缺点,以及使用场景 如下表格:

隔离级别         

优点

缺点 使用场景
未提交读                      读数据的时候,不申请共享锁,所以不会被阻塞 读到的数据,可能会脏读,不一致。 如做年度,月度统计报表,数据不一定要非常精确
已提交读       比较折中,而且是推荐的默认设置 有可能会阻塞,在一个事务里,多次读取相同的数据行,得到的结果可能不同。 一般业务都是使用此场景
可重复读 在一个事务里,多次读取相同的数据行,得到的结果可保证一致、 更严重的阻塞,在一个事务里,读取符合某查询的行数,会有变化(这是因为事务里允许新增)  如当我们在事务里需要,多次统计查询范围条件行数, 做精确逻辑运算时,需要考虑逻辑是否会前后不一致.
可序列化 最严重格的数据保护,读取符合某查询的行数,不会有变化(不允许新增)。 其它事务的增,删,改,查 范围内都会阻塞  如当我们在写事务时,不用考虑新增数据带来的逻辑错误。
行版本控制已提交读

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,能读到新数据
大多情况下行版本控制的已提交读比快照隔离更受欢迎:
1、RCSI比SI占用更少的tempdb空间 。
2、RCSI支持分布式事务,而SI不支持 。
3、RCSI不会产生更新冲突 。
4、RCSI无需再应用程序端作任何修改。唯一要更改的只是一个数据库选项。

写与写还是会阻塞,行版本是存放在tempdb里,数据修改的越多,需要

存储的信息越多,维护行版本就

需要越多的的开销

如果默认方式阻塞比较严重,推荐用行版本控制已提交读,改善性能
快照隔离

阻塞大大减少(读与读不阻塞,读与写不阻塞)

阻塞减少,有可能读到旧数据
1、不太可能由于更新冲突而导致事务必须回滚得情况
2、需要基于运行时间长、能保证时间点一致性的多语句来生成报表的情况

维护行版本需要额外开销,且可能读到旧的数据 允许读取稍微比较旧版本信息的情况下

  2.2 锁的隔离级别(补充)

    了解了事务的隔离级别,锁也是有隔离级别的,只是它针对是单独的sql查询。下面包括显示如下

     select  COUNT(1) from dbo.product(HOLDLOCK)

HOLDLOCK

在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。

与SERIALIZABLE一样

NOLOCK

不添加共享锁和排它锁,仅应用于SELECT语句

与READ UNCOMMITTED一样

PAGLOCK

指定添加页锁(否则通常可能添加表锁)。 

READPAST

跳过已经加锁的数据行, 仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作

ROWLOCK

使用行级锁,而不使用粒度更粗的页级锁和表级锁

建议中用在UPDATE和DELETE语句中。

TABLOCKX

表上使用排它锁, 这个锁可以阻止其他事务读或更新这个表的数据

UPDLOCK

指定在读表中数据时设置更新锁(update lock)而不是设置共享锁,作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改

declare @b1 bit
declare @b2 bit 

set @b1=1
set @b2=0

-- right,return 0
select iif(@b1=@b2,1,0)

--error,The data types bit and bit are incompatible in the add operator.
select @b1+@b2

    As we all know,it's a common sense that separate reading and writing operations can immensely increse the performance of MySQL database.Especially the query operations by executing select statement relevant with large tables.Therefore,we usually choose a proxy tool to deal with it.There're a lot of tools can be used nowadays such as mycat(by Apache),dble(based on mycat by Action),atlas,dbproxy(based on atlas of Qihoo360 by MeituanDianping),cetus(by NetEase) and so forth.I'm not going to compare who's the better tool to use.I'm just prefer to having a test on another popular tool which is called "ProxySQL".

五.分布式事务

      分布式事务是跨越两个或多个称为资源管理器的服务器。 称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。在 .NET Framework 中,分布式事务通过 System.Transactions 命名空间中的 API 进行管理。 如果涉及多个永久资源管理器,System.Transactions API 会将分布式事务处理委托给事务监视器,例如 Microsoft 分布式事务协调程序 (MS DTC),在Windows服务里该服务叫Distributed Transaction Coordinator 默认未启动。

  在sql server里 分布式是通过BEGIN DISTRIBUTED TRANSACTION 的T-SQL来实现,是分布式事务处理协调器 (MS DTC) 管理的 Microsoft 分布式事务的起点。执行 BEGIN DISTRIBUTED TRANSACTION 语句的 SQL Server 数据库引擎的实例是事务创建者。并控制事务的完成。 当为会话发出后续 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句时,控制事务实例请求 MS DTC 在所涉及的所有实例间管理分布式事务的完成(事务级别的快照隔离不支持分布式事务)。

在执行T-sql里 查询多个数据库主要是通过引用链接服务器的分布式查询,下面添加了RemoteServer链接服务器

USE AdventureWorks2012;  
GO  
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
-- Delete candidate from remote instance.  
DELETE RemoteServer.AdventureWorks2012.HumanResources.JobCandidate  
    WHERE JobCandidateID = 13;  
COMMIT TRANSACTION;  
GO  

在设计Table Schema时,要实现三大目标:占用空间少,查询速度快,更新速度快。这三个目标有些千丝万缕的关联,设计良好的Table Schema,都会实现,反之,设计差的Table Schema,都不能实现。

 

六.事务死锁

   6.1 在关系型数据库里都有死锁的概念,在并发访问量高时,事务里或者T-sql大批量操作(特别是修改删除结果集),都有可能导致死锁。死锁是由两个互相阻塞的线程组成也称为抱死。sql server死锁监视器进程会定期检查死锁,默认间隔为5秒,会自动判断将回滚开销影响最少的事务作为死锁牺牲者,并收到1025 错误,消息模板来自master.dbo.sysmessages表的where error=1205。当发生死锁时要了解两方进程的sessionid各是多少, 各会话的查询语句,冲突资源是什么。请查看死锁的分析排查。

   会产生死锁的资源主要是:锁 (就是上篇讲的数据行,页,表等资源),其它的死锁包括如:1. 工作者线程调度程序或CLR同步对象。2.两个线程需要更多内存,但获得授权前一个必须等待另一个。3.同一个查询的并行线程。4.多动态结果集(MARS)资源线程内部冲突。这四种很少出现死锁,重点只要关注锁资源带来的死锁。

    6.2 下面事务锁资源产生死锁的原理:

     1. 事务T1和事务T2 分别占用共享锁RID第1行和共享锁RID第2行。

     2. 事务T1更新RID2试图获取X阻塞,事务T2更新RID2试图获取X阻塞。

     3.  事务各自占有共享锁未释放,而要申请对方X锁会排斥一切锁

图片 1

 6.3 死锁与阻塞的区别

  阻塞是指:当一个事务请求一个资源尝试获取锁时,被其它事务锁定,请求的事务会一直等待,直到其它事务把该锁释放,这就发生了阻塞,默认情况sqlserver会一直等下去。所以阻塞往往能持续很长时间,这对程序的并发性能影响很大。

  死锁是两个或多个进程之间的相互等待,一般在5秒就会检测出来,消除死锁。并发性能不像阻塞那么严重。

  阻塞是单向的,互相阻塞就变成了死锁。

 6.3 尽量避免死锁的方法

  按同一顺序访问对象

  避免事务中的用户交互

  保持事务简短

  合理使用隔离级别

  调整语句的执行计划,减少锁的申请数目。  

内存是访问速度最快的存储介质,如果数据全部存储在内存中,那会极大的提高数据库系统的吞吐量,但是,每个数据库系统能够使用的内存有限,为了提高查询性能,SQL Server将最近使用过的数据驻留在内存中。SQL Server 查询的数据必须在内存中,如果目标数据页不在内存中,那么SQL Server会将数据从Disk读取到内存中。SQL Server 响应时间跟数据加载很大的关系,如果加载的数据集占用的空间小,数据页分布集中,那么SQL Server使用预读机制,能够很快将数据加载到内存,相应地,SQL Server的响应时间会很小。

Introduce

七.事务并发检查

  在检查并发方面,有很多种方式像原来的如sp_who,sp_who2等系统存储过程,perfmon计数器,sql Trace/profiler工具等,检测和分析并发问题,还包括sql server 2005以及以上的:

   DMV  特别是sys.dm_os_wait_stats和sys.dm_os_waiting_tasks ,这里简单讲下并发检查

        例如:查询用户会话的相关信息

     SELECT  blocking_session_id FROM sys.dm_os_waiting_tasks WHERE session_id>50

    blocking_session_id 阻塞会话值有时为负数: 

    -2 :被阻塞资源属于孤立分布式事务。

    -3: 被阻塞资源属于递延恢复事务。

    -4: 对于锁存器等待,内锁存器状态转换阻止了session的识别。

  例如:下面查询阻塞超5秒的等待

      SELECT blocking_session_id FROM sys.dm_os_waiting_tasks WHERE wait_duration_ms>5000

  例如:只关注锁的阻塞,可以查看sys.dm_tran_locks
    SELECT * FROM sys.dm_tran_locks WHERE request_status='wait'

        通过sys.dm_exec_requests查看用户请求

        通过sqlDiag.exe收集运行系统的信息

        通过errorlog里打开跟踪标识1222 来分析死锁

        通过sys.sysprocess 检测阻塞。

       

图片 2

 

创建索引能够提高查询性能,其实是因为,索引字段比Base Table的字段少,索引结构占用的存储空间小,SQL Server 加载索引结构的耗时少。由于索引结构是有序的,避免了全表扫描,也能提高查询性能。使用窄的数据类型,使用数据压缩,创建BTree索引,创建ClumnStore 索引,都能减少数据集占用的存储空间,提高数据加载到内存的速度。SQL Server在执行用户的查询请求时,每一行数据都必须在内存中,因此,数据集占用的空间越少,加载的过程越快,SQL Server的查询性能越高。

    ProxySQL is a low-weight proxy tool based on a SQLite database.It provids hight performance espcially in high concurrent environment what we can see below(compared with the MaxScale).

一,窄的数据行会节省存储空间,减少IO次数

 

使用窄的数据类型,使行的宽度达到最小,在存储相同数据量时,能够节省存储空间,减少Disk IO的次数。

图片 3

在存储日期数据时,Date占用3Byte,DateTime占用8Byte,DateTime2(2)占用6Byte,DateTime2(4)占用7Byte,DateTime2(7)占用8Byte。不管从表示的精度上,还是从占用的存储空间上来看,DateTime2(N)都完胜DateTime。

 

例如,存储‘yyyy-mm-dd MM:HH:SS’格式的日期数据,有以下4中选择:

    

  • 使用字符串 varchar(19) 或 nvarchar(19)存储,十分不明智,前者占用19Byte后再占用38Byte;
  • 使用数据类型 datetime2(7)存储,占用8Byte,虽然精度更高,但是毫秒都是0,浪费存储空间;
  • 使用数据类型 datetime存储,占用8Byte,如果需要存储毫秒,datetime不满足;
  • 使用数据类型 datetime2(2)存储,占用6Byte,相比较是最理想的。

*    The configuration of ProxySQL is a three-layer structure:*

由于SQL Server存储数据是按照row存储数据的,每个Page能够存储的数据行是有限的。在查询同等数量的数据行时,如果row宽度窄,那么每个page会容纳更多的数据行,不仅减少IO次数,而且节省存储空间。

 

二,在窄的数据列上创建index,能够提高查询性能

图片 4

在窄的数据列上创建Index,索引结构占用的存储空间更小,SQL Server消耗更少的Disk IO就能将索引结构加载到内存中,能够提高查询性能。

 

在创建Index时,必须慎重选择聚集索引键,主要有两个原因

  *  We usually conifuge the parameter in layer of memory,and then load them into layer of runtime to make it take effect.In the end,we should save them to disk for durability storage.It also provides some simple syntax to transfer configurations between those layers as below:*

1,聚集索引其实就是表本身,SQL Server必须保持物理存储顺序和逻辑存储顺序一致

 

在SQL Server中,Clustered Index能够确定Table的物理存储,使Table的物理存储顺序和聚集索引键的逻辑顺序保持一致。在对Table数据进行update时,如果更新聚集索引键,导致数据行所在聚集索引键必须移动,此时,SQL Server不能“原地更新”数据行,必须将数据行移动到其应有的物理位置上,Table的物理存储顺序和聚集索引键的逻辑顺序才能保持一致。SQL Server将Update命令拆分成等价的delete命令和insert 命令。

  • LOAD MYSQL object FROM MEMORY or LOAD MYSQL object TO RUNTIME 
  • SAVE MYSQL object TO MEMORY or SAVE MYSQL object FROM RUNTIME 
  • LOAD MYSQL object TO MEMORY or LOAD MYSQL object FROM DISK 
  • SAVE MYSQL object FROM MEMORY or SAVE MYSQL object TO DISK 
  • LOAD MYSQL object FROM CONFIG

示例:聚集索引键4被修改为8,那么,SQL Server将数据行5删除,然后再相应的位置上插入数据行8。

 

图片 5

The comparison with other popular middleware tools.

如果插入的位置上没有多余的存储空间,那么,插入操作会导致页拆分,产生索引碎片,影响查询性能。

图片 6

 

2,NonClustered Index的叶子节点中,都包含Clustered Index键。

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:选择合适的数据类型,锁与事务拨云见日

上一篇:InnoDB意向锁和插入意向锁,触发器详解 下一篇:没有了
猜你喜欢
热门排行
精彩图文