数据库相关

一些做法
# 把一些存储的N'去掉，防止过多的锁
# 使用select * from a with(updlock) where来处理getUserInfo存储
# 查询是否有死锁现象




一些重要链接

Itepub-SqlServer网站
http://www.itepub.net/html/kaifawendang/shujuku/SQL_Server/index.html

SqlServer教程集合
http://www.chinaitlab.com/www/special/sql01.asp

其实所有的死锁最深层的原因就是一个:资源竞争
表现一:
一个用户A 访问表A(锁住了表A),然后又访问表B
另一个用户B 访问表B(锁住了表B),然后企图访问表A
这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B,才能继续，好了他老人家就只好老老实实在这等了
同样用户B要等用户A释放表A才能继续这就死锁了
解决方法：
这种死锁是由于你的程序的BUG产生的，除了调整你的程序的逻辑别无他法
仔细分析你程序的逻辑，
1：尽量避免同时锁定两个资源
2: 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源.

表现二:
用户A读一条纪录，然后修改该条纪录
这是用户B修改该条纪录
这里用户A的事务里锁的性质由共享锁企图上升到独占锁(for update),而用户B里的独占锁由于A有共享锁存在所以必须等A释
放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。
这种死锁比较隐蔽，但其实在稍大点的项目中经常发生。
解决方法:
让用户A的事务（即先读后写类型的操作),在select 时就是用Update lock
语法如下：
select * from table1 with(updlock) where ....

---

事务死锁的解决途径
　　错误现象：加了事务处理的程序在运行时偶尔会出现“事务（进程 ID 60）与另一个进程已被死锁在 lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务”的报错。

　　解决方法一：程序优化。在程序中压缩事务的执行时间，不发生数据变更的SQL不放在事务段执行。

　　解决方法二：数据库优化。用事件探察器追踪到发生DeadLock的语句，看看语句的执行是否有异常，比如执行时间太长，调整数据结构，该加索引就加索引。

---

4月13日
 
 
 
由一个大写N造成的SQL SERVER死锁
 
SQL SERVER死锁的问题断断续续追踪了两三个星期，终于有个初步的判断：有一个SELECT语句和一个UPDATE语句需要获取大量的锁。死锁应该与之相关。
应用程序在SQL中为每个传入的字符串参数加了N，表示是unicode字符串。在参数与相应的列进行匹配的时候，如果该列不是NVARCHAR而是VARCHAR类型，则SQL SERVER要对该列的数据进行转换，由于此转换而导致不能使用索引，会获取大量的键锁、页锁。如果几个这样的SQL同时执行则容易死锁。
比如这样几句SQL：
declare @P1 int
set @P1=14
exec sp_prepexec @P1 output, N'@P1 nvarchar(4000)', N'SELECT CMDOCUMENTS.*, CMDOCTYPES.DOCTYPENAME, CMDOCCONTENTS.CONTENTSIZE, CMDOCCONTENTS.MIMETYPE FROM CMDOCUMENTS INNER JOIN CMDOCTYPES ON CMDOCUMENTS.DOCTYPEID = CMDOCTYPES.DOCTYPEID LEFT OUTER JOIN CMDOCCONTENTS ON CMDOCUMENTS.DOCID = CMDOCCONTENTS.DOCID WHERE CMDOCUMENTS.DOCID = @P1   ORDER BY CMDOCUMENTS.DOCNAME ASC', N'c373e90a87fa3a628f6acd567805f1f0'
select @P1
这是用事件探查器跟踪到的。
其中只有SELECT....是在java应用中写的SQL，前面的declare、set、exec以及后面的select @P1都应该是jdbc生成的。
declare @P1 int声明了一个int变量P1
set @P!=NULL 把P1赋值为空
exec sp_prepexec ....是执行SQL SERVER的存储过程sp_prepexec。@P1是一个输出参数，在存储过程执行后，P1会得到SQL SERVER分配的句柄。之后就可以用exec sp_execute引用这个句柄来再次执行此SQL。比如：
exec sp_execute 450, N'c373e90a87fa3a5f8f6acd567805f1f0', 2
这就是执行一个前面已分配句柄的SQL,句柄是450，后面是SQL中需要使用的参数。
好，现在说N。前面的几个SQL中可以看到在字符串参数值的前面有一个大写的N，它的含义是表明后面引号里的字符串是unicode，如果我没判断错的话，应该就是UTF-8。
为什么能判断出有了这个N之后就会获取大量的锁呢？是借助于查询分析器。
我把以下SQL贴到查询分析器中
SELECT * FROM CMDOCUMENTS WHERE CMDOCUMENTS.DOCID = N'c373e90a87fa3a7c8f6acd567805f1f0'
然后“显示估计的查询计划”
在CMDOCUMENTS.PK_...中看到对全部的索引做了扫描（scan），还有“成本100%”，并且其中“参数：”一栏的内容引起了我的怀疑，内容如下：
OBJECT:([v23test83].[v23test83].[CMDOCUMENTS].[PK_CMDOCUMENTS]),WHERE(Convert([CMDOCUMENTS].[DOCID])=[@1])
可以看到WHERE后面有对列DOCID使用了个函数convert，一般的数据库，如果一旦对列使用了函数就无法使用建立在该列上的索引，除非建立的是函数索引。
看表结构，DOCID是VARCHAR类型的。
把N去掉，再做分析
SELECT * FROM CMDOCUMENTS WHERE CMDOCUMENTS.DOCID = 'c373e90a87fa3a7c8f6acd567805f1f0'
仍然是“成本100%”，但是convert不见了。
使用事件探查器跟踪。有N的情况下，此查询会获取大量的锁。没有N的情况下，只获取非常少量的锁。
如果不去掉N，但把DOCID改为NVARCHAR类型，查询也不会获取大量的锁。
所以在SQL SERVER中使用N可要慎重。

---

SQL server锁的机制

SQL server的所有活动都会产生锁。锁定的单元越小，就越能越能提高并发处理能力，但是管理锁的开销越大。如何找到平衡点，使并发性和性能都可接受是SQL Server的难点。
SQL Server有如下几种琐：
1、 共享锁
用于只读操作(SELECT)，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。
2、 更新锁
更新锁是一种意图锁，当一个事物已经请求共享琐后并试图请求一个独占锁的时候发生更新琐。例如当两个事物在几行数据行上都使用了共享锁，并同时试图获取独占锁以执行更新操作时，就发生了死锁：都在等待对方释放共享锁而实现独占锁。更新锁的目的是只让一个事物获得更新锁，防止这种情况的发生。
3、 独占锁
一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享缩。写是独占锁，可以有效的防止’脏读’
4、 意图缩
在使用共享锁和独占锁之前，使用意图锁。从表的层次上查看意图锁，以判断事物能否获得共享锁和独占锁，提高了系统的性能，不需从爷或者行上检查。
5、 计划锁
Sch-M,Sch-S。对数据库结构改变时用Sch-M，对查询进行编译时用Sch-S。这两种锁不会阻塞任何事物锁，包括独占锁。

读是共享锁，写是排他锁，先读后更新的操作是更新锁，更新锁成功并且改变了数据时更新锁升级到排他锁。锁的类型有：
DB-----数据库，由于 dbid 列已包含数据库的数据库 ID，所以没有提供任何信息
FIL----文件
IDX----索引
PG-----页，数据或索引页。页码。页由 fileid:page 组合进行标识，其中，fileid 是 sysfiles 表中的 fileid，而 page 是该文件内的逻辑页码。
KEY----键,用于保护可串行事务中的键范围
TAB----表，包括所有数据和索引在内的整个表。由于 ObjId 列已包含表的对象 ID，所以没有提供任何信息
EXT----区域, 相邻的八个数据页或索引页构成的一组。正被锁定的扩展盘区中的第一个页码。页由 fileid:page 组合进行标识
RID----行，表内已锁定行的行标识符。行由 fileid:page:rid 组合进行标识，其中，rid 是页中的行标识符

锁的状态：
Grant---能使用被授权的资源
Wait----能使用被其他任务阻塞的资源
Cnvrt---Convert,锁正在被转换

细分锁的模式：
0 Null 没有得到资源的访问权限
1 Sch-S (Schema stability) 对查询进行编译时。能防止加锁的对象被删除直到解锁
2 Sch-M (Schema Modification) 改变数据库结构时发生。能防止其他的事物访问加锁的对象
3 IS (Intent Shares) 意图共享锁。
4 SIU(Share Intent Update) 意图在维护资源的共享锁时，把更新锁放到锁层次结构的下层资源上
5 IS-S(Intent Share-shared) 复合键范围锁
6 IX(Intent Exclusive) 意图排他锁
7 SIX(Share Intent Exclusive)
8 S(Share) 共享锁
9 U(Update) 更新锁。防止死锁
10 Iin-Nul(Intent Insert-Null) 索引行层次的锁定，复合键范围锁
11 IS-X(Intent Share-Exclusive)
12 IU(Intent Update) 意图更新锁
13 IS-U(Intent Share Update) 串行更新扫描
14 X(Exclusive) 排他锁
15 BU 块操作使用的锁

所以有如下的结论。

1、一个连接在修改数据块时别的连接不能修改这个数据块，直到解锁。
并行访问是任何数据库解决方案都最为重视的问题了，为了解决并行访问方面的问题各类数据库系统提出了各种各样的方案。SQL Server采用了多线程机制，它当然能够一次处理多个请求。不过，在用户修改数据的情况下并行访问问题就变得复杂起来了。显然，数据库通常只允许唯一用户一次修改特定的数据。当某一用户开始修改某块数据时， SQL Server能很快地锁定数据，阻止其他用户对这块数据进行更新，直到修改该数据的第一位用户完成其操作并提交交易或者回滚。但是，当某一位用户正在修改某块数据时假设另一位用户又正想查询该数据的信息时会发生什么情况呢？
2、通常情况下，一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改。这个方案会降低系统的性能和效率，尽管现在是行级锁（7.0以前是锁页甚至是锁表），如果你一次修改多行数据，SQL Server则会把数据锁定范围提升到页级别乃至锁定整个数据表，从而不必针对每一记录跟踪和维护各自的数据锁，这样能加快修改的速度，消耗小的服务器资源，但是并发性就差了。。
3、一个连接写的时候，另一个连接可以写，但是不得读
4、多个连接可以同时读同一行。

所以锁发生在读、写的竞争上。

5、设置事物的级别 SET TRANSACTION ISOLATION LEVEL
A、READ COMMITTED ：指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 SQL Server 的默认值。
B、READ UNCOMMITTED：执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现在数据集中或从数据集消失。这是四个隔离级别中限制最小的级别。
C、REPEATABLE READ：锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。
D、SERIALIZABLE：在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。
注释
一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 FROM 子句中在表级上指定优化选项。
SET TRANSACTION ISOLATION LEVEL 的设置是在执行或运行时设置，而不是在分析时设置。

---

如何将数据库中被锁表解锁
字体大小: 小
 中
 大
 

LINK:8000余本电子图书免费下载

 

 我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在pl/sql Developer工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就更难了，下面这叫查询语句可以查询出所以被锁的会话。如下：
SELECT   sn.username, m.SID,sn.SERIAL#, m.TYPE,
         DECODE (m.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 lmode, LTRIM (TO_CHAR (lmode, '990'))
                ) lmode,
         DECODE (m.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 request, LTRIM (TO_CHAR (m.request, '990'))
                ) request,
         m.id1, m.id2
    FROM v$session sn, v$lock m
   WHERE (sn.SID = m.SID AND m.request != 0)         --存在锁请求，即被阻塞
      OR (    sn.SID = m.SID                         --不存在锁请求，但是锁定的对象被其他会话请求锁定
          AND m.request = 0
          AND lmode != 4
          AND (id1, id2) IN (
                        SELECT s.id1, s.id2
                          FROM v$lock s
                         WHERE request != 0 AND s.id1 = m.id1
                               AND s.id2 = m.id2)
         )
ORDER BY id1, id2, m.request;

通过以上查询知道了sid和 SERIAL#就可以开杀了
   alter system kill session 'sid,SERIAL#';