ORACLE与MSSQL内核的差异--锁、并发

关于锁与并发
锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。 我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读和幻觉读：1．丢失更新：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新，这将导致数据丢失。例如，两个编辑人员制作了同一文档的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文档。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则可以避免该问题。2. 脏读：就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。例如，一个编辑人员正在更改电子文档。在更改过程中，另一个编辑人员复制了该文档（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文档。分发给用户的文档包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前任何人都不能读取更改的文档，则可以避免该问题。3．不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。4．幻觉读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。
所以，处理多用户并发访问的方法是加锁。锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。当一个用户锁住数据库中的某个对象时，其他用户就不能再访问该对象。加锁对并发访问的影响体现在锁的粒度上。

SQL server锁的机制[读阻塞写，写阻塞读，尽可能多地commit以释放lock!]
为了控制锁定的资源，应该首先了解系统的空间管理。在SQL Server 2000系统中，最小的空间管理单位是页，一个页有8K。所有的数据、日志、索引都存放在页上。另外，使用页有一个限制，这就是表中的一行数据必须在同一个页上，不能跨页。页上面的空间管理单位是盘区，一个盘区是8个连续的页。表和索引的最小占用单位是盘区。数据库是由一个或者多个表或者索引组成，即是由多个盘区组成。放在一个表上的锁限制对整个表的并发访问放在盘区上的锁限制了对整个盘区的访问放在数据页上的锁限制了对整个数据页的访问放在行上的锁只限制对该行的并发访问。
SQL Server 2000 具有多粒度锁定，允许一个事务锁定不同类型的的资源。为了使锁定的成本减至最少，SQL Server 自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以增加并发但需要较大的开销，因为如果锁定了许多行，则需要控制更多的锁。锁定在较大的粒度（例如表）就并发而言是相当昂贵的，因为锁定整个表限制了其他事务对表中任意部分进行访问，但要求的开销较低，因为需要维护的锁较少。SQL Server 可以锁定行、页、扩展盘区、表、库等资源。
行是可以锁定的最小空间, 行级锁占用的数据资源最少，所以在事务的处理过程中，允许其他事务继续操纵同一个表或者同一个页的其他数据，大大降低了其他事务等待处理的时间，提高了系统的并发性。
页级锁是指在事务的操纵过程中，无论事务处理数据的多少，每一次都锁定一页，在这个页上的数据不能被其他事务操纵。在SQL Server 7.0以前，使用的是页级锁。页级锁锁定的资源比行级锁锁定的数据资源多。在页级锁中，即使是一个事务只操纵页上的一行数据，那么该页上的其他数据行也不能被其他事务使用。因此，当使用页级锁时，会出现数据的浪费现象，也就是说，在同一个页上会出现数据被占用却没有使用的现象。在这种现象中，数据的浪费最多不超过一个页上的数据行。
表级锁也是一个非常重要的锁。表级锁是指事务在操纵某一个表的数据时，锁定了这个数据所在的整个表，其他事务不能访问该表中的其他数据。当事务处理的数据量比较大时，一般使用表级锁。表级锁的特点是使用比较少的系统资源，但是却占用比较多的数据资源。与行级锁和页级锁相比，表级锁占用的系统资源例如内存比较少，但是占用的数据资源却是最大。在表级锁时，有可能出现数据的大量浪费现象，因为表级锁锁定整个表，那么其他的事务都不能操纵表中的其他数据。
盘区锁是一种特殊类型的锁，只能用在一些特殊的情况下。簇级锁就是指事务占用一个盘区，这个盘区不能同时被其他事务占用。例如在创建数据库和创建表时，系统分配物理空间时使用这种类型的锁。系统是按照盘区分配空间的。当系统分配空间时，使用盘区锁，防止其他事务同时使用同一个盘区。当系统完成分配空间之后，就不再使用这种类型的盘区锁。特别是，当涉及到对数据操作的事务时，不使用盘区锁。
数据库级锁是指锁定整个数据库，防止任何用户或者事务对锁定的数据库进行访问。数据库级锁是一种非常特殊的锁，它只是用于数据库的恢复操作过程中。这种等级的锁是一种最高等级的锁，因为它控制整个数据库的操作。只要对数据库进行恢复操作，那么就需要设置数据库为单用户模式，这样系统就能防止其他用户对该数据库进行各种操作。
行级锁是一种最优锁，因为行级锁不可能出现数据既被占用又没有使用的浪费现象。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在SQL Server中，还支持锁升级(lock escalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQL Server中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。
1. 一个连接在修改数据块时别的连接不能修改这个数据块，直到解锁。并发访问是任何数据库解决方案都最为重视的问题了，为了解决发访问方面的问题各类数据库系统提出了各种各样的方案。SQL Server采用了多线程机制，它当然能够一次处理多个请求。不过，在用户修改数据的情况下并发访问问题就变得复杂起来了。显然，数据库通常只允许唯一用户一次修改特定的数据。当某一用户开始修改某块数据时， SQL Server能很快地锁定数据，阻止其他用户对这块数据进行更新，直到修改该数据的第一位用户完成其操作并提交交易或者回滚。但是，当某一位用户正在修改某块数据时假设另一位用户又正想查询该数据的信息时会发生什么情况呢？
2. 通常情况下，一个连接在修改数据块时别的连接也不能查询这个数据块，直到解锁。反之亦然：读的时候不能写和修改。这个方案会降低系统的性能和效率，尽管现在是行级锁（7.0以前是锁页甚至是锁表），如果你一次修改多行数据，SQL Server则会把数据锁定范围提升到页级别乃至锁定整个数据表，从而不必针对每一记录跟踪和维护各自的数据锁，这样能加快修改的速度，消耗小的服务器资源，但是并发性就差了。。
3. 一个连接写的时候，另一个连接可以写，但是不得读。多个连接可以同时读同一行。所以锁发生在读、写的竞争上。
4. 设置事物的级别 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 的设置是在执行或运行时设置，而不是在分析时设置。

传统锁管理器其他数据库（Sybase SQLserver）使用的传统锁管理器：
锁定一行的过程：
找出要锁定行的地址
进入锁定管理器（必须串行化，它是通常的内存结构）
锁定列表
检查列表，查看是否有其他用户锁定了该行
在列表中创建一个新项，建立已经锁定了该行的事实
解除对列表的锁定，现在已经将该行锁定，你获得修改权，之后，当提交时，必须：
再次进入，
锁定那个锁定的列表
检查它，并释放用户的所有锁定
解除对列表的锁定
可见，需要的锁定越多，在此操作上花费的时间就越多。导致这些数据库中需要尽可能多地使用commit，以释放这些稀有资源，而Oracle不使用这种锁管理器。Oracle的锁、并发机制Oracle的锁处理方式是：
找出要锁定行的地址
到该行
锁定它（如果它已经被锁定，就等待它，除非使用nowait选项）
仅此而已，锁被存储为数据的一个属性，Oracle不需要传统的锁管理器。锁在Oracle里不是稀有资源，在Oracle里不需要多余的comit。
MSSQL与oracle的内核区别简述之：
列表式管理，本地式管理；
读阻塞写、写阻塞读，读写不阻塞；
锁有开销，锁几乎无开销；
尽可能快地commit，按需commit！
=================================================

对ORACLE定义的这7个级别的锁，我是这样理解的

LMODE -- Lock mode in which the session holds the lock:

0 - none ---- 无
1 - null (NULL) ---- 可能某些情况下，如分布式数据库的查询会产生此锁。
2 - row-S (SS) ---- 表结构共享锁
3 - row-X (SX) ---- 表结构共享锁+被操作的记录的排它锁（若有DML操作）
4 - share (S) ---- 表结构共享锁+所有记录共享锁（隐含）
5 - S/Row-X (SSX) ---- 表结构共享锁+所有记录排它锁（隐含）
6 - exclusive (X) ---- 表结构排它锁

ORACLE的文档中，对SS锁是这样解释的：

Row Share Table Locks (RS)
A row share table lock (also sometimes called a subshare table lock, SS) indicates that the
transaction holding the lock on the table has locked rows in the table and intends to update
them. A row share table lock is automatically acquired for a table when one of the following
SQL statements is executed:

SELECT ... FROM table ... FOR UPDATE OF ... ;

LOCK TABLE table IN ROW SHARE MODE; 按照上面的解释：是ORACLE锁住某表及该表的某些记录，准备（倾向）修改这些记录。
但尝试以 LOCK TABLE table IN ROW SHARE MODE 来锁住某表后，查看V$LOCK视图，发现只有TM=2的锁，而并未对哪些记录加了S锁；比较SELECT FOR UPDATE，发现除了TM=2的锁外，还存在TX=6的锁，并且通过TX=6中的ID1，ID2，可在回滚段中查看到被加了X锁的记录。此外，ORACLE文档中也说明，被SS的表（TM=2的锁），其中的记录可以被DML，但不允许对该表加X锁；这说明，该表的记录并未被加S锁（因为可以修改记录），而只是表结构加了S锁（因为不可以对表加X锁），故此，认定，
SS锁实际就是对表加S锁。
对于RX锁，通过DML操作后得知：DML操作会产生TX=6 和 TM=3的锁，从TX=6中的ID1，ID2，可在回滚段中查看到被加了X锁的记录，而这些记录在未提交/回滚之前，是不可以再被修改的。通过对该表，除了这些记录被加X锁外，别的记录仍可以被DML，这说明，表被加了S锁，被操作的记录被加了X锁。因此认定，RS实际就是：表结构共享锁+被操作的记录的排它锁。当只是对表加RX锁时候。

S锁：由于加了S锁的表，无法对其数据进行DML操作，故猜测，S锁实际是对表结构和表记录加了共享锁。对于加了S锁后，仍可以对表执行SELECT FOR UPDATE，我是这样理解的：SELECT FOR UPDATE是比较特殊的一种情况：由于其不修改记录，所以对这些记录而言，并未产生真正的排它锁，这与DML操作产生的排它锁相比，是有差别的；但由于不允许在这些记录“再”加SELECT FOR UPDATE，所以，也不是纯粹的共享锁，我认为其是介于共享锁和排它锁之间的一种特殊的情况，因此，ORACLE在对表加了S锁后，还允许对该表执行SELCT FOR UPDATE，但实际上，此时此语句已经无意义了，因为加了S锁的表不允许DML操作，故，此语句于普通的SELECT 语句无差别。

SRX锁，第4级别是S锁（表结构共享锁+所有记录共享锁），第6级别是X锁（表结构排它锁+所有记录排它锁），而根据锁级别不断升高，锁的限制不断加强的规律判断，SRX--第5层锁，只能是：表结构共享锁+所有记录排它锁 这种情况。但目前，还想不出在什么情况下会使用这样的锁？

X锁：表结构排它锁。其实，表结构加排它锁后，表记录自然也算是加了排它锁，因为此时，表记录无法加共享锁，只能只读。故，此锁级别最高，通常使用在修改表结构或删除表时使用。（表注1）：引用 itpub.net 上的 jeffli73 写的：oracle多粒度封锁机制研究（论坛）.doc

需要澄清两个概念

表级锁(TABLE LOCK)与数据字典锁(data dictionary lock,DDL)在ORACLE中是两个不同的概念,ZALBB朋友谈到的所谓“表结构共享锁”、“表结构排它锁”，从汉语字面理解应该属于数据字典锁(data dictionary lock,DDL)，但ZALBB朋友又将其与表级锁混在一起谈，我想是不妥的。

ORACLE在引入意向锁后，其表级锁主要有5种，任何一个DML操作，总是先获得某种类型的表级锁，才可能去获得行级锁。如：
SESSION1 发出SELECT * FROM DEPT WHERE DEPTNO=10 FOR UPDATE；首先在表级获得RX锁，表示该事务要以SELECT FOR UPDATE 方式访问该表的某些记录，获得该锁后，将DEPTNO=10 的记录上的锁标志置位，可以理解为在行上加X锁；之后，SESSION2发出UPDATE DEPT SET LOC=LOC WHERE DEPTNO=10，它首先要求在表级获得RX锁（该锁表示事务要修改某些行），与SESSION1获得的RS锁可以相容，故该表级锁可以获得，之后SESSION2试图获得DEPTNO=10的行级锁，但被SESSION1已获得的锁阻塞；如果SESSION2发出的不是UPDATE，而是DROP TABLE DEPT，则事务既要获得排它的DDL锁（注意，不是表级锁，是数据字典锁），还要获得DEPT表级的X锁（DDL operations also acquire DML locks (data locks) on the schema object to be
modified.同样出自ORACLE CONCEPTS，另外实验也可以证明），而X锁与SESSION1获得的RS锁不相容，故DROP不成功。而如果SESSION2发出的是lock table dept in EXCLUSIVE MODE,也会因与RS锁不相容，被阻塞。所以说，DML锁两个主要作用：保护数据、防止DDL操作。应该指出的是，ORACLE多数的DML操作，其实际的封锁粒度在行级，表级只是加了起到表示下级封锁情况的意向锁（RS或RX），但因此就说表级锁就是表结构锁，我想肯定是不合适的。关于“意向锁”，我一直在说其本质意义在于效率，是因为：如果没有RS或RX锁，我们UPDATE一行数据只在行上加X锁，那么如果其它的DML或DDL要在表级加X锁，那么它就不仅要查看表级加锁的情况，还要查看各记录的加锁情况，对于拥有很多记录的表，其消耗恐怕是无法容忍的。所以，要引入“意向锁”来表示下面行的加锁情况。