SQLServer学习笔记系列8

 

一.写在前面的话

最近一直在思考一个问题,什么才能让我们不显得浮躁,真正的静下心来,用心去感受,用心去回答每个人的问题,用心去帮助别人。现实的生活,往往让我们显得精疲力尽,然后我们仔细想过没用,其实支持我们一直走下去的其实是心里的那一份希冀,希望未来会更好,用一个逼格比较高的词语,或许那就是理想。那么,有人会说,什么是理想,因为生活的重担已将我们所谓的理想磨灭了。但是,那又何妨了,而今,我已经被打败过了,我用曾经的飞蛾扑火,换来今天手心里握着的一把余温尚存的灰烬。值得庆幸的是,我依然没有忘记,这把灰烬的名字叫做理想。顺势而为的同时对事物执着而坚定,向着目标努力,理想也许也就不远了,所以理想还是要有的,万一实现了呢?

                         人的活动如果没有理想的鼓舞,就会变得空虚而渺小。 —— 车尔尼雪夫斯基

 

二.表的约束

在我们创建表的时候,为了保证数据的正确性和完整性,通常我们需要对数据进行校验,也就是我们这里说到的表的约束。其中常用的表的约束包括:

(1)主键的约束。

(2)唯一的约束。

(3)非空的约束。

(4)检查的约束。

(5)外键的约束

这里我们首先创建一张班级表,同时建立对应的约束:

其中班级的id和班级名称分别定义了主键约束和唯一约束,主键约束保证了表中只存在唯一一列,唯一约束确保该列字段值不能重复,必须唯一。

接着我们定义一张学生表,也建立相应的约束。

通过建表我们可以看到,其中学生的编号(studid)定义为identity,意思是字段自增长并且从100开始增长,每次增长1。其中性别(gender)存在一个检查约束,保证插入到数据表里面的数据只存在(‘男’,‘女’)两种方式,避免其他的脏数据插入。

再说一下比较重要的一个约束——外键约束。外键约束,表示一个表中的数据依靠另一个表,其中存在关联关系。正如上面的两张表一样,一个学生表中可以定义一个班级iD,来区别这个学生隶属于哪个班级。那么可以这样定义:

也就是说,只有在班级表里面存在的班级编号(classid),在学生表里面才可以出现。避免学生找不到班级的情况。这样以后班级表和学生表通过外键classid,建立了一种关系。假如我们要删除班级表时,系统会报错,因为在学生表(Students)里面存在外键关联。故必须先删除学生表再删除班级表。

 

三.事务(Transaction)

1.所谓事务,即作为单个工作单元而执行的一系列操作。也就是完成某一件事情,需要包含很多步骤,所有的步骤完成以后,那么这个事情才算完成,其中任何一步出现问题,这个事情就不可能完成。这里所说的一系列步骤,看成一个整体,那么就是一个事务。

(1)隐式事务

隐式事务是隐藏的,也就是每执行一次DML操作,就直接提交到数据库保存。我们先看看货运公司表的数据:

1 SELECT * FROM sales.Shippers

比如我们的删除操作,针对货运公司表(sales.shippers),我们删除shipperid为6号的公司:

1  DELETE FROM Sales.Shippers WHERE shipperid=6

删除以后,我们对比之前的记录,发现确实将6号货运公司删除掉了,这就是隐式事务,默认已经提交了,影响是永久的。

(2)显示事务

显示事务,就是明确的指出事务的起始边界。可以通过回滚操作(rollback)和提交来处理事务的结果。显示事务的好处就是可以确保执行的完整性,比如银行的转账,要么成功,要么失败,采取回滚操作,回到原来的状态。

定义事务的边界,事务开始用begin transaction——》操作(包含增删改)——》提交(commit)或者回滚(rollback)。

接着我们同样采取删除操作,删除货运公司表里面货运公司号为5,6的公司。那么我们要保证5,6号公司要么全部删除,要么就都不删除,所以需要用到事务。sql如下:

1        BEGIN TRANSACTION;
2        
3         DELETE FROM Sales.Shippers WHERE shipperid=4;
4         DELETE FROM Sales.Shippers WHERE shipperid=5;
5         
6         ROLLBACK;

我们先开始一个事务,然后进行删除操作,最后执行rollback操作,数据库表回到以前的状态,虽然进行了删除操作,但是rollback回滚,让数据回到以前状态。

假如我们执行的是commit操作的话,那么数据就无法回滚。因为已提交到数据库保存。

1        BEGIN TRANSACTION;
2        
3         DELETE FROM Sales.Shippers WHERE shipperid=4;
4         DELETE FROM Sales.Shippers WHERE shipperid=5;
5         
6         COMMIT;

 

2.事务的属性(ACID)

(1)原子性。事务务必是原子工作单元,这就意味着事务中进行的操作,要么全部执行,那么全都不执行。

(2)一致性。也就是要么都更新,要么都不更新。比如我们要将货运公司编号为1,2的公司名称更改为abc,xyz,那么我们在事务中可以这样执行:

 

1        BEGIN TRANSACTION;
2        
3         UPDATE  Sales.Shippers SET companyname='abc' WHERE shipperid=1;
4         UPDATE  Sales.Shippers SET companyname='XYZ' WHERE shipperid=2;

 

我们再新建一个查询窗口,查询一下更新的结果:

那么我们提交以后,就可以查询出来结果,并且确实已经将1,2号货运公司的名称更改了。

1        BEGIN TRANSACTION;
2        
3         UPDATE  Sales.Shippers SET companyname='abc' WHERE shipperid=1;
4         UPDATE  Sales.Shippers SET companyname='XYZ' WHERE shipperid=2;
5         
6         COMMIT;

这就反应了事务的一致性,只有两个都更新完了,才能执行其他的操作。

(3)隔离性。其实上面的也反映了一种隔离性,也就是说隔离性,让我们看不到事务内部如何一步一步的更新的,只有事务提交以后,才能看到统一的结果。类似于上面的更新1,2号货运公司公司名称,只有当他们都更新完了,我们才看到更新的结果。

(4)持久性。提交到数据库保存数据,对数据库是持久性的,影响永久的。

四.锁定(lock)和阻塞

锁是事务获取的一种控制资源,用于保护数据资源,防止其他事务对数据进行冲突或不兼容的访问。

(1)排他锁

自己独自占有资源,别的事务无法访问。

(2)共享锁

共享资源,都可以访问,每个人各自享用数据资源。

为了说明锁的应用场景,我们新建三个查询窗口,进程号分别为52,53,54:

在52窗口中,我们新建一个事务来执行,对产品表中产品编号为2的产品的单价增加一块钱。

1 BEGIN TRANSACTION;
2 
3 UPDATE Production.Products 
4 SET unitprice=unitprice+1
5 WHERE productid=2;

为了更新这一行,那么会话必定会获得一个排他锁,这样才能保证更新的完成。那么此时我们试着在53号窗口,进行同一行记录的查询结果为怎样了?

在此处就体现了排他锁和共享锁。要得到有关锁的执行信息,我们可以在54号窗口中,进行相关的查询,即动态管理视图(sys.dm_tran_locks)。

复制代码
 1 SELECT 
 2  request_session_id, --进程ID
 3  resource_type,     --资源类型
 4  resource_database_id,
 5  DB_NAME(request_session_id),
 6 resource_description,
 7 resource_associated_entity_id,
 8 request_mode,
 9 request_status
10  
11 FROM sys.dm_tran_locks
复制代码

为了获得更多与阻塞相关的链接信息,那么我们可以在54号窗口中接着再查询sys.dm_exec_connections动态管理视图。

复制代码
1 SELECT
2 session_id,
3 connect_time,
4 last_read,
5 last_write,
6 most_recent_sql_handle
7 FROM sys.dm_exec_connections
8 WHERE session_id IN(52,53);
复制代码

执行结果中,我们可以看到链接时间,以及最后读、写的时间,还有执行sql的编号。

为了更加细致的看出结果,我们还可以查出系统执行的sql语句。

1 UPDATE Production.Products   SET unitprice=unitprice+1  WHERE productid=2;

查询结果中,我们可以看到系统执行的sql语句:

有时候我们为了排除某一阻塞,我们可以查询出哪些进程被堵塞了,那么我们就可以通过相应的操作,将阻塞解除。利用sql可以这样查询:

复制代码
 1 SELECT 
 2 session_id,
 3 blocking_session_id,
 4 command,
 5 sql_handle,
 6 database_id,
 7 wait_type,
 8 wait_time,
 9 wait_resource
10 FROM  sys.dm_exec_requests
11 WHERE blocking_session_id>0;
复制代码

查询结果可以看出53号进程被52号进程给堵塞住了,同时可看到等待的时间,以及等待的资源。

这样52号进程一直在等待,为了使进程释放出来,不一直等下去,那么我们可以设置超时,超过响应设置的时间,则结束会话。设置超时可以这样设置:

1     SET LOCK_TIMEOUT 5000;
2     
3     SELECT * FROM  Production.Products 
4     WHERE productid=2;

设置以后,那么超过请求时间,则结束回话。

其中设置(set locktimeout -1)为无限等待。默认为-1;同时有另一种结束回话的用法kill。可以直接结束掉相应的进程号。

例如我们结束掉52号进程,(kiil 52)。

1 KILL 52;

今天先学习到这里,下次接着学习锁所产生的隔离级别。

posted @ 2015-06-10 17:13  跋锋寒LT  阅读(230)  评论(0编辑  收藏  举报