oracle数据库SQL防止超长,SQL Server和Oracle防止数据锁定的比较

关键字：ORACLE, SQL SERVER, 锁定, Before Image, ISOLATION LEVEL, NOLOCK, ORA-01555

一、ORACLE 非阻塞查询与Before Image一个用户对表数据进行修改，但尚未提交；

另一个用户仍然可以自由地对表进行非阻塞查询，查询结果是提交前的数据，即 ISOLATION LEVEL READ COMMITED。

但如果后者试图修改正在被前者修改的数据，就会被立即锁住，等待前者提交或放弃修改。

这里使用的是ORACLE所特有的非阻塞查询技术：Before Image/Rollback Segment/UNDO Tablespace

！长时间运行的查询会遇到的错误情形分析：用户A启动事务，修改某行记录，但还没有提交；此时会产生Before Image数据。

在查询启动时，修改还没有提交。

用户B启动一个SELECT查询，逐行返回记录，其中就将包括被用户A所修改的记录...

一段时间后，用户A提交了事务；

又一段时间后，这行记录的Before Image数据空间被其它数据覆盖；

最后，用户B的查询游标终于来到这行记录，但此时这行记录的在用户B开始查询时刻的Before Image数据已经不存在了，查询就会失败，返回著名的 ORA-01555 Snapshot too old 错误。

二、SQL SERVER的情形

一个用户对表数据进行修改，但尚未提交；另一个用户查询 select count(*) from t 也会被锁定！

这对于一直使用ORACLE，刚接触SQL SERVER的用户，是很难理解的。其根本原因就在于SQL SERVER没有类似 Before Image 的机制。

对此SQL SERVER的解决办法：

(1)使用行级锁。

好像较新版本的SQL SERVER(2000以及后续版本？)会自动使用行级锁。这样，虽然全部查询会被锁定等待，但查询其它行会立即得到结果。

(2)不太严谨的做法是使用 NOLOCK

写法: select ... from ... with(NOLOCK)

效果: 不会被锁定，立即返回结果。

看起来不错，是吗？但是，等等，效果根本不能与ORACLE相比！

NOLOCK等价于 ISOLATION LEVEL READ UNCOMMITED. 这会产生很多问题，必须谨慎使用！[@more@]

开发SQL SERVER数据库应用的原则：

基于以上分析，原则为：应当尽量写短小的事务处理语句，以免长时间锁定其它事务。

写到这里，似乎明白了SQL SERVER缺省事务行为为何与ORACLE不相同了：ORACLE: 缺省非Auto COMMIT，直到显示提交为止。

MS SQL: 缺省是Auto COMMIT，每条语句执行后，自动提交。