Sqlserver2000中的并发问题-数据库专栏,SQL Server

sqlserver2000中的并发问题一、并发问题的产生：
如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：
丢失或覆盖更新。未确认的相关性（脏读）。不一致的分析（非重复读）。幻像读。 1、丢失更新：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新，这将导致数据丢失。
例如，两个编辑人员制作了同一文件的电子复本。每个编辑人员独立地更改其复本，然后保存更改后的复本，这样就覆盖了原始文件。最后保存其更改复本的编辑人员覆盖了第一个编辑人员所做的更改。如果在第一个编辑人员完成之后第二个编辑人员才能进行更改，则能避免该问题。
2、未确认的相关性（脏读）
当第二个事务选择其他事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。

例如，一个编辑人员正在更改电子文件。在更改过程中，另一个编辑人员复制了该文件（该复本包含到目前为止所做的全部更改）并将其分发给预期的用户。此后，第一个编辑人员认为目前所做的更改是错误的，于是删除了所做的编辑并保存了文件。分发给用户的文件包含不再存在的编辑内容，并且这些编辑内容应认为从未存在过。如果在第一个编辑人员确定最终更改前所有人都不能读取更改的文件，则能避免该问题。
3、不一致的分析（非重复读）
当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析和未确认的相关性类似，因为其他事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其他事务更改；因而该行被非重复读取。

例如，一个编辑人员两次读取同一文件，但在两次读取之间，作者重写了该文件。当编辑人员第二次读取文件时，文件已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才能读取文件，则能避免该问题。
4、幻像读
当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。

例如，一个编辑人员更改作者提交的文件，但当生产部门将其更改内容合并到该文件的主复本时，发现作者已将未编辑的新材料添加到该文件中。如果在编辑人员和生产部门完成对原始文件的处理之前，所有人都不能将新材料添加到文件中，则能避免该问题。
二、并发问题的解决方案：
当锁定用作并发控制机制时，他能解决并发问题。这使所有事务得以在彼此完全隔离的环境中运行，不过所有时候都能有多个正在运行的事务。

可串行性是通过运行一组并发事务达到的数据库状态，等同于这组事务按某种顺序连续执行时所达到的数据库状态。
sql-92 隔离级别
尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要，然而许多事务并不总是需求完全的隔离。例如，多个作者工作于同一本书的不同章节。新章节能在任意时候提交到项目中。不过，对于已编辑过的章节，没有编辑人员的批准，作者不能对此章节进行所有更改。这样，尽管有未编辑的新章节，但编辑人员仍能确保在任意时间该书籍项目的正确性。编辑人员能查看以前编辑的章节及最近提交的章节。

事务准备接受不一致数据的级别称为隔离级别。隔离级别是个事务必须和其他事务进行隔离的程度。较低的隔离级别能增加并发，但代价是降低数据的正确性。相反，较高的隔离级别能确保数据的正确性，但可能对并发产生负面影响。应用程式需求的隔离级别确定了 sql server 使用的锁定行为。

sql-92 定义了下列四种隔离级别，sql server 支持所有这些隔离级别：
未提交读（事务隔离的最低级别，仅可确保不读取物理损坏的数据）。提交读（sql server 默认级别）。可重复读。可串行读（事务隔离的最高级别，事务之间完全隔离）。
如果事务在可串行读隔离级别上运行，则能确保所有并发重叠事务均是串行的。

下面四种隔离级别允许不同类型的行为。

隔离级别

脏读

不可重复读取

幻像

 

 

未提交读

是

是

是

提交读

否

是

是

可重复读

否

否

是

可串行读

否

否

否

 

事务必须运行于可重复读或更高的隔离级别以防止丢失更新。当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。如果两个事务使用一个 update 语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。

三、问题解决的实现：
1、       默认解决方案：set transaction isolation level
控制由连接发出的所有 microsoft® sql server™ select 语句的默认事务锁定行为。
语法
set transaction isolation level
    { read committed
        | read uncommitted
        | repeatable read
        | serializable
    }
参数
read committed

指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数据。该选项是 sql server 的默认值。

read uncommitted

执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排他锁。当设置该选项时，能对数据执行未提交读或脏读；在事务结束前能更改数据内的数值，行也能出目前数据集中或从数据集消失。该选项的作用和在事务内所有语句中的所有表上设置 nolock 相同。这是四个隔离级别中限制最小的级别。

repeatable read

锁定查询中使用的所有数据以防止其他用户更新数据，不过其他用户能将新的幻像行插入数据集，且幻像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所以应只在必要时才使用该选项。

serializable

在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。该选项的作用和在事务内所有 select 语句中的所有表上设置 holdlock 相同。
注释
一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的 from 子句中在表级上指定优化选项。

set transaction isolation level 的设置是在执行或运行时设置，而不是在分析时设置。
示例
下例为会话设置 transaction isolation level。对于每个后续 transact-sql 语句，sql server 将所有共享锁一直控制到事务结束为止。

set transaction isolation level repeatable read

go

begin transaction

select * from publishers

select * from authors

...

commit transaction
2、自定义事务隔离级别
默认情况下，microsoft® sql server™ 2000 在 read committed 的一个隔离级别上操作。不过，应用程式可能必须运行于不同的隔离级别。若要在应用程式中使用更严格或较宽松的隔离级别，能通过使用 set transaction isolation level 语句设置会话的隔离级别，来自定义整个会话的锁定。

指定隔离级别后，sql server 会话中所有 select 语句的锁定行为都运行于该隔离级别上，并一直保持有效直到会话终止或将隔离级别设置为另一个级别。例如，若要设置事务隔离级别为可串行读，以确保并发事务不能在 authors 表中插入幻像行，请执行：

use pubs

go

set transaction isolation level serializable

go

begin transaction

select au_lname from authors



说明  如果必要，能通过指定表级锁定提示来替代单个 select 语句的隔离级别。指定表级锁定提示不会影响会话中的其他语句。建议仅在绝对必要时才使用表级锁定提示更改默认的锁定行为。

若要确定当前设置的事务隔离级别，请使用 dbcc useroptions 语句，例如：

use pubs

go

set transaction isolation level repeatable read

go

dbcc useroptions

go

set option

value

textsize

4096

language

us_english

dateformat

mdy

datefirst

7

isolation level

repeatable read

 

(5 rows affected)

dbcc execution completed. if dbcc printed error messages, see your system administrator.
3、调整事务隔离级别
隔离属性是 acid 的四个属性之一，逻辑工作单元必须具有这四个属性才能称为事务。该属性能够使事务免受其他并发事务所执行的更新的影响。每个事务的隔离级别实际上都是能自定义的。

microsoft® sql server™ 支持 sql-92 中定义的事务隔离级别。设置事务隔离级别虽然使程式员承担了某些完整性问题所带来的风险，但能换取对数据更大的并发访问权。和以前的隔离级别相比，每个隔离级别都提供了更大的隔离性，但这是通过在更长的时间内占用更多限制锁换来的。事务隔离级别有：
read uncommittedread committedrepeatable readserializable
能使用 transact-sql 或通过数据库 api 来设置事务隔离级别：

transact-sql

transact-sql 脚本和 db-library 应用程式使用 set transaction isolation level 语句。

ado

ado 应用程式将 connection 对象的 isolationlevel 属性设置为 adxactreaduncommitted、adxactreadcommitted、adxactrepeatableread 或 adxactreadserializable。

ole db

ole db 应用程式调用 itransactionlocal::starttransaction，其中 isolevel 设置为 isolationlevel_readuncommitted、isolationlevel_readcommitted、isolationlevel_repeatableread 或 isolationlevel_serializable。

odbc

odbc 应用程式调用 sqlsetconnectattr，其中 attribute 设置为 sql_attr_txn_isolation，valueptr 设置为 sql_txn_read_uncommitted、sql_txn_read_committed、sql_txn_repeatable_read 或 sql_txn_serializable。