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;

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