第 9章．事务处理
9,1 引言
9,2 Transact-SQL语句和事务处理
9,3 锁定
9.1 引言
事务 (Transaction)与锁定 (Lock)可以确保数据在存储或修改过程中受到其他用户的中断时,能够正确地被存储,修改,而不会造成数据因被存储或修改到一半而导致数据不完整 。
事务是 SQL Server中的执行单元,它由一系列 T-SQL语句组成 。 事务处理必须满足 ACID原则 。 ACID指原子性 (Atomicity),一致性 (Consistency),隔离性 (Iso1ation)
与持久性 (Durability)。
事务的这种机制保证了一个事务或者提交后成功执行,
或者提交后失败滚回 。 也就是说,执行事务的结果或是将数据所要执行的操作全部完成,或是全部数据一点也不修改 。
返回目录
9.2 Transact-SQL语句和事务处理
事务是一种机制,是一个操作序列,它包含了一组数据库操作命令,即由一系列 T-SQL语句组成 。 SQL Server的事务可分为两类,：
系统提供的事务和用户定义的事务 。
系统提供的事务是指在执行某些 T-SQL语句时,一条语句就构成了一个事务,这些语句是：
1.ALTER TABLE
2.CREATE
3.DELETE
4.DROP
5.REVOKE
6.SELECT
7.TRUNCATE TABLE
8.INSERT
8.OPEN
9.UPDATE
11.GRANT
12.FETCH 返回目录
9.2 Transact-SQL语句和事务处理
例如执行创建表的语句：
Create table users
(
id int not null，
username varchar(20)，
password varchar(20)
)
这条语句本身就构成了一个事务,它要么建立起含 3列的表结构,
要么对数据库没有任何影响 。
在实际应用中,经常使用的是用户自定义的事务 。 事务的定义方法是：用 BEGIN TRANSACTION 命令来标识一个事务的开始,
用 COMMIT TRANSACTION或 ROLLBACK TRANSACTION命令来标识事务的结束 。 这两个命令之间的所有语句被视为一体,只有执行到 COMMIT TRANSACTION 命令时,事务中对数据库的更新操作才算确认 。 返回目录
1.BEGINTRANSACTION语句
BEGIN TRANSACTION语句定义事务的开始,其语法格式为：
BEGIN TRAN[SACTION] [transaction_name |
@tran_name_variable
[WITH MARK[‘description’]]
其中,参数 transaction_name指定事务的名称,必须遵循标识符规则,字符数不超过 32 个字符 。
@tran_name_variable表示用户定义的,含有效事务名称的变量 。 变量只能声明为 CHAR,VARCHAR、
NCHAR 或 NVARCHAR 类型 。 WITH MARK指定在日志中标记事务,description是描述该标记的字符串 。
BEGIN TRANSACTION 语 句 的 执 行 使 全 局 变 量
@@TRANCOUNT的值加 1。 (@@TRANCOUNT是用来计算当前连接中现有事务的数目 。 ) 返回目录
2,COMMITTRANSACTION语句
COMMIT语句标志一个事务的结束,同时也是提交语句,
它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分 。 其语法格式为：
COMMIT [ TRAN[SACTION] [transaction_name |
@tran_name_variable] ]
其中,参数 transaction_name和 @tran_name_variable分别表示事务名称和事务变量名 。 COMMITTRANSACTION语句的执行使全局变量 @@TRANCOUNT的值减 1。
标志一个事务的结束也可以使用 COMMIT WORK语句,其语法格式为：
COMMIT[WORK]
它与 COMMIT TRANSACTION语句的差别在于 COMMIT
WORK语句不带参数 。
返 回目录
【 9.1】 定义一个事务,删除 demo数据库的 users表中的一行数据 。
BEGIN TRAN
USE demo
DELETE FORM users
WHERE username＝ ’ chen’
COMMIT TRAN
GO
返回目录
9.3 锁定
SQL Server 2000通过锁定 (Lock)来确保事务完整性和数据库一致性 。 锁定可以防止丢失更新,读取尚未认可的数据,不可重复读和幻象读取 (Phantomread)。
9.3.1 锁定的概念
锁定 （ Lock） 是在多用户环境下对资源访问的一种限制机制,当对一个数据源加锁后,此数据源就有了一定的访问限制,就称对此数据源进行了锁定 。 在 SQL Server中,可以对以下的对象进行锁定：数据行 （ Row）,索引行 （ Key）,页 （ Page）,盘区
（ Extent）,表 （ Table）,数据库 （ Database） 。
在 SQL Server 2000中,拥有多个数据点的锁定,来允许事务锁定不同类型的资源 。 为了把锁定的成本降至最低,SQL Server自动根据工作的适当层级来锁定资源 。
返回目录
9.3.2 锁定的类别
在 SQL Server 2000中,使用不同的锁模式锁定资源,这些锁模式确定了并发事务访问资源的方式 。 锁定按两种不同分类方法共有 5
种锁模式 。
1.从数据库系统的角度来看
锁定分为以下三种类型,共享锁 （ Shared Lock）,更新锁 （ Update
Lock）,独占锁 （ ExclusiveLock）
2.从程序员的角度看
锁定分为以下两种类型,乐观锁 （ Optimistic Lock）,悲观锁
（ PessimisticLock） 。
返回目录
9.3.3 查看锁定信息
在 SQL Server 2000中,可以通过企业管理器或存储过程 sp_lock来查看锁定信息 。
1,利用 Enterprise Manager 查看锁
2,用系统存储过程 Sp_lock查看锁
存储过程 Sp_lock 的语法格式如下：
sp_lock [[@spid1=]’spid1’] [,[@spid2=]’spid2’]
spid是 System Process ID,即系统进程编号的缩写,可以在 master.dbo.sysprocesses 系统表中查到 。 spid是 INT
类型的数据,如果不指定 spid则显示所有的锁 。
【 例 9.2】 显示当前系统中所有的锁
use master
exec sp_lock
运行结果就会显示 master中所有的锁 。 返回目录
9.3.4 死锁及其防止
死锁 （ Deadlocking） 是当两个事务分别锁定某个资源,
而又分别等待对方释放其锁定的资源时,将发生死锁 。
死锁会造成资源的大量浪费,甚至会使系统崩溃 。 在
SQL Server 2000中会自动定期搜索,并根据各会话的死锁优先级结束一个代价最低的事务,然后将其事务回滚,同时向执行此进程的程序发送编号为 1205的错误信息 。 防止死锁的途径就是不能让满足死锁条件的情况发生,返回目录
9,1 引言
9,2 Transact-SQL语句和事务处理
9,3 锁定
9.1 引言
事务 (Transaction)与锁定 (Lock)可以确保数据在存储或修改过程中受到其他用户的中断时,能够正确地被存储,修改,而不会造成数据因被存储或修改到一半而导致数据不完整 。
事务是 SQL Server中的执行单元,它由一系列 T-SQL语句组成 。 事务处理必须满足 ACID原则 。 ACID指原子性 (Atomicity),一致性 (Consistency),隔离性 (Iso1ation)
与持久性 (Durability)。
事务的这种机制保证了一个事务或者提交后成功执行,
或者提交后失败滚回 。 也就是说,执行事务的结果或是将数据所要执行的操作全部完成,或是全部数据一点也不修改 。
返回目录
9.2 Transact-SQL语句和事务处理
事务是一种机制,是一个操作序列,它包含了一组数据库操作命令,即由一系列 T-SQL语句组成 。 SQL Server的事务可分为两类,：
系统提供的事务和用户定义的事务 。
系统提供的事务是指在执行某些 T-SQL语句时,一条语句就构成了一个事务,这些语句是：
1.ALTER TABLE
2.CREATE
3.DELETE
4.DROP
5.REVOKE
6.SELECT
7.TRUNCATE TABLE
8.INSERT
8.OPEN
9.UPDATE
11.GRANT
12.FETCH 返回目录
9.2 Transact-SQL语句和事务处理
例如执行创建表的语句：
Create table users
(
id int not null，
username varchar(20)，
password varchar(20)
)
这条语句本身就构成了一个事务,它要么建立起含 3列的表结构,
要么对数据库没有任何影响 。
在实际应用中,经常使用的是用户自定义的事务 。 事务的定义方法是：用 BEGIN TRANSACTION 命令来标识一个事务的开始,
用 COMMIT TRANSACTION或 ROLLBACK TRANSACTION命令来标识事务的结束 。 这两个命令之间的所有语句被视为一体,只有执行到 COMMIT TRANSACTION 命令时,事务中对数据库的更新操作才算确认 。 返回目录
1.BEGINTRANSACTION语句
BEGIN TRANSACTION语句定义事务的开始,其语法格式为：
BEGIN TRAN[SACTION] [transaction_name |
@tran_name_variable
[WITH MARK[‘description’]]
其中,参数 transaction_name指定事务的名称,必须遵循标识符规则,字符数不超过 32 个字符 。
@tran_name_variable表示用户定义的,含有效事务名称的变量 。 变量只能声明为 CHAR,VARCHAR、
NCHAR 或 NVARCHAR 类型 。 WITH MARK指定在日志中标记事务,description是描述该标记的字符串 。
BEGIN TRANSACTION 语 句 的 执 行 使 全 局 变 量
@@TRANCOUNT的值加 1。 (@@TRANCOUNT是用来计算当前连接中现有事务的数目 。 ) 返回目录
2,COMMITTRANSACTION语句
COMMIT语句标志一个事务的结束,同时也是提交语句,
它使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分 。 其语法格式为：
COMMIT [ TRAN[SACTION] [transaction_name |
@tran_name_variable] ]
其中,参数 transaction_name和 @tran_name_variable分别表示事务名称和事务变量名 。 COMMITTRANSACTION语句的执行使全局变量 @@TRANCOUNT的值减 1。
标志一个事务的结束也可以使用 COMMIT WORK语句,其语法格式为：
COMMIT[WORK]
它与 COMMIT TRANSACTION语句的差别在于 COMMIT
WORK语句不带参数 。
返 回目录
【 9.1】 定义一个事务,删除 demo数据库的 users表中的一行数据 。
BEGIN TRAN
USE demo
DELETE FORM users
WHERE username＝ ’ chen’
COMMIT TRAN
GO
返回目录
9.3 锁定
SQL Server 2000通过锁定 (Lock)来确保事务完整性和数据库一致性 。 锁定可以防止丢失更新,读取尚未认可的数据,不可重复读和幻象读取 (Phantomread)。
9.3.1 锁定的概念
锁定 （ Lock） 是在多用户环境下对资源访问的一种限制机制,当对一个数据源加锁后,此数据源就有了一定的访问限制,就称对此数据源进行了锁定 。 在 SQL Server中,可以对以下的对象进行锁定：数据行 （ Row）,索引行 （ Key）,页 （ Page）,盘区
（ Extent）,表 （ Table）,数据库 （ Database） 。
在 SQL Server 2000中,拥有多个数据点的锁定,来允许事务锁定不同类型的资源 。 为了把锁定的成本降至最低,SQL Server自动根据工作的适当层级来锁定资源 。
返回目录
9.3.2 锁定的类别
在 SQL Server 2000中,使用不同的锁模式锁定资源,这些锁模式确定了并发事务访问资源的方式 。 锁定按两种不同分类方法共有 5
种锁模式 。
1.从数据库系统的角度来看
锁定分为以下三种类型,共享锁 （ Shared Lock）,更新锁 （ Update
Lock）,独占锁 （ ExclusiveLock）
2.从程序员的角度看
锁定分为以下两种类型,乐观锁 （ Optimistic Lock）,悲观锁
（ PessimisticLock） 。
返回目录
9.3.3 查看锁定信息
在 SQL Server 2000中,可以通过企业管理器或存储过程 sp_lock来查看锁定信息 。
1,利用 Enterprise Manager 查看锁
2,用系统存储过程 Sp_lock查看锁
存储过程 Sp_lock 的语法格式如下：
sp_lock [[@spid1=]’spid1’] [,[@spid2=]’spid2’]
spid是 System Process ID,即系统进程编号的缩写,可以在 master.dbo.sysprocesses 系统表中查到 。 spid是 INT
类型的数据,如果不指定 spid则显示所有的锁 。
【 例 9.2】 显示当前系统中所有的锁
use master
exec sp_lock
运行结果就会显示 master中所有的锁 。 返回目录
9.3.4 死锁及其防止
死锁 （ Deadlocking） 是当两个事务分别锁定某个资源,
而又分别等待对方释放其锁定的资源时,将发生死锁 。
死锁会造成资源的大量浪费,甚至会使系统崩溃 。 在
SQL Server 2000中会自动定期搜索,并根据各会话的死锁优先级结束一个代价最低的事务,然后将其事务回滚,同时向执行此进程的程序发送编号为 1205的错误信息 。 防止死锁的途径就是不能让满足死锁条件的情况发生,返回目录
