如何在mysql 的存储过程中使用事务

67 MySQL 事务与锁定命令

671 BEGIN/COMMIT/ROLLBACK 句法

缺省的，MySQL 运行在 autocommit 模式。这就意味着，当你执行完一个更新时，MySQL 将立刻将更新存储到磁盘上。

如果你使用事务安全表 (例如 InnoDB、BDB)，通过下面的命令，你可以设置 MySQL 为非 autocommit 模式：

SET AUTOCOMMIT=0

在此之后，你必须使用 COMMIT 来存储你的更改到磁盘上，或者使用 ROLLBACK ，如果你希望忽略从你的事务开始所做的更改。

如果你希望为一系列语句从 AUTOCOMMIT 模式转换，你可以使用 START TRANSACTION 或 BEGIN 或 BEGIN WORK 语句：

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

START TRANSACTION 在 MySQL 4011 中被加入；这是被推荐的开始一个特别(ad-hoc)事务的方式，因为这是 ANSI SQL 句法。

注意，如果你使用的是一个非事务安全表，更改会立刻被存储，不受 autocommit 模式状态的约束。

当你更新了一个非事务表后，如果你执行一个 ROLLBACK，你将得到一个错误 (ER_WARNING_NOT_COMPLETE_ROLLBACK) 作为一个警告。所有事务安全表将被恢复，但是非事务安全表将不会改变。

如果你使用 START TRANSACTION 或 SET AUTOCOMMIT=0，你应该使用 MySQL

二进制日志做备份以代替老的更新日志。事务处理被以一个大块形式存储在二进制日志中，在 COMMIT

上面，为了保护回滚的事务，而不是被存储的。查看章节 494 二进制日志。 如果您使用起动事务处理或集AUTOCOMMIT=0

，您应该使用MySQL 二进制日志为备份代替更旧的更新日志。 事务处理存储在二进制登录一大块，做，保证, 滚的事务处理不存储。 参见部分4

。94 二进制日志。

下列命令自动的结束一个事务 (就好像你在执行这个命令之前，做了一个 COMMIT)：

命令 命令 命令

ALTER TABLE BEGIN CREATE INDEX

DROP DATABASE DROP TABLE RENAME TABLE

TRUNCATE

你可以使用 SET TRANSACTION ISOLATION LEVEL 改变事务的隔离级。查看章节 673 SET TRANSACTION 句法。

672 LOCK TABLES/UNLOCK TABLES 句法

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ]

UNLOCK TABLES

LOCK TABLES 为当前线程锁定表。UNLOCK TABLES 释放当前线程拥有的所有锁定。当线程发出另一个 LOCK TABLES，或当与服务器的连接被关闭时，被当前线程锁定的所有表将被自动地解锁。

为了在 MySQL 402 使用 LOCK TABLES ，你必须拥有一个全局的 LOCK TABLES 权限和一个在相关表上的

SELECT 权限。在 MySQL 323 中，你对该表需要有 SELECT、insert、DELETE 和 UPDATE 权限。

使用 LOCK TABLES 的主要原因是，仿效事务处理或在更新表时得到更快的速度。此后会有更详细的描述。

如果一个线程在一个表上得到一个 READ 锁，该线程 (和所有其它线程) 只能从表中读取。如果一个线程在一个表上得到一个 WRITE 锁，那么只有拥有这个锁的线程可以从表中读取和写表。其它的线程被阻塞。

READ LOCAL 和 READ 之间的不同就在于，当锁被加载时，READ LOCAL 允许非冲突(non-conflicting) INSERT 语句执行。如果当你加载着锁时从 MySQL 外部 *** 作数据库文件，这将仍不能被使用。

当你使用 LOCK TABLES 是地，你必须锁定所有你将使用的表，并且必须使用与你的查询中将使用的别名相同！如果你在一个查询中多次使用一个表(用别名)，你必须为每一个别名获得一个锁。

WRITE 锁通过比 READ 锁有更高的权限，以确保更新被尽快地处理。这就意味着，如果一个线程获得一个 READ

锁，而同时另外一个线程请求一个 WRITE 锁，并发的 READ 锁请求将等待直到 WRITE 线程得到了锁并释放了它。你可以使用

LOW_PRIORITY WRITE 锁，当该线程在等待 WRITE 锁时，它将允许其它的线程获得 READ 锁。 你应该只使用

LOW_PRIORITY WRITE 锁，如果你确信这将是最后一次，当没有线程将拥有 READ 锁。

LOCK TABLES 工作如下：

以内部定义的次序排序所有被锁定的表 (从用户立场说，该次序是不明确的)。

如果一个表被以一个读锁和一个写锁锁定，将写锁放在读锁之前。

一次只锁定一个表，只到线程得到所有的锁定。

这个方案是为了确保，表锁定死锁释放。 对于这个模式你仍然有些其它事情需要知道：

如果你对一个表使用一个 LOW_PRIORITY WRITE 锁定，这就意味着，MySQL 将等待这个锁，直到没有线程请求一个 READ

锁。当线程得到了 WRITE 锁，并等待获得锁定表列表中的下一个表的锁定时，其它所有的线程将等待 WRITE

锁被释放。如果这在你的应用程序中会引起一个严重的问题，你应该考虑将你的某些表转换为事务安全表。

你可以使用 KILL 安全地杀死一个正在表锁定的线程。查看章节 455 KILL 句法。

注意，你不应该 锁定你正在对其使用 INSERT DELAYED 的表。这是因为，在这种情况下，INSERT 是通过单独的线程完成的。

通常，你不需要锁定任何表，因为所有单 UPDATE 语句都是原子的；其它的线程无法干扰当前执行的 SQL 语句。当你无论如何希望锁定表时，这里有一些情况：

如果你在一束表上运行许多 *** 作，锁定你将要使用的表，这会更快一些。当然有不利的方面，其它线程将不能更新一个 READ

锁的表，并且没有其它线程要以读取一个 WRITE 锁的表。 在 LOCK TABLES 下，某些事运行得更快一些的原因是，MySQL

将不会转储清除被锁定表键高速缓冲，直到 UNLOCK TABLES 被调用 (通常键高速缓冲在每个 SQL 语句后都会被转储清除)。这将加速在

MyISAM 表上的插入、更新、删除。

如果你在 MySQL 中正在使用一个不支持事务的存储引擎，如果你希望能确保没有其它的线程会出现在一个 SELECT 和 一个 UPDATE 之间，你必须使用 LOCK TABLES 。下面的示例显示为了安全地执行，这里需要LOCK TABLES ：

mysql> LOCK TABLES trans READ, customer WRITE;

mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;

mysql> UPDATE customer SET total_value=sum_from_previous_statement

-> WHERE customer_id=some_id;

mysql> UNLOCK TABLES;

不使用 LOCK TABLES，将可能发生在 SELECT 和 UPDATE 语句执行期间有另外一个线程可能在 trans 表中插入一行新记录。

通过使用递增更新 (UPDATE customer SET value=value+new_value) 或 LAST_INSERT_ID() 函数，你可以在很多情况下避免使用 LOCK TABLES。

你也可以使用用户级锁定函数 GET_LOCK() 和 RELEASE_LOCK() 解决一些情况，这些锁被保存在服务器上的一个哈希表中，并以

pthread_mutex_lock() 和 pthread_mutex_unlock() 实现以获得高速度。查看章节 6362

辅助功能函数。

查看章节 531 MySQL 如何锁定表，以获取关于锁定方案的更多信息。

你可以使用 FLUSH TABLES WITH READ LOCK 命令以读锁锁定所有数据库中的所有表。查看章节 453 FLUSH 句法。如果你有一个可以及时建立文件快照的文件系统，例如 Veritas，这将是得到备份的非常方便方式。

注意：LOCK TABLES 不是事务安全的，在尝试锁定一个表之前，将自动地提交所有的活动事务。

673 SET TRANSACTION 句法

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

设置全局的、整个会话或下一个事务的事务隔离级。

缺省行为是设置下一个(未启动的)事务的隔离级。如果你使用 GLOBAL

关键词，语句为所有在那个点上建立的新连接设置默认的全局事务隔离级。为了这样做，你需要有 SUPER 权限。使用 SESSION

关键词为当前连接所有将来执行的事务设置默认的事务隔离级。

你可以使用 --transaction-isolation= 为 mysqld 设置默认的全局隔离级。查看章节 411 mysqld 命令行选项

一、启用FileTable

1、修改数据库引擎的属性

打开“SQL Server配置管理器”，修改SQL Server数据库引擎的属性。使用此页可针对此 Microsoft SQL Server 2012安装启用 FILESTREAM。

（1）针对 Transact-SQL 访问启用 FILESTREAM

选中此项可针对 Transact-SQL 访问启用 FILESTREAM。 必须选中此控制选项，才能使用其他控制选项。如果不启用此选项，就不能添加FileStream文件组。

（2）针对文件 I/O 流访问启用 FILESTREAM

选中此项可针对 FILESTREAM 启用 Win32 流访问。

（3）Windows 共享名

使用此控制选项可输入将用来存储 FILESTREAM 数据的 Windows 共享的名称。默认为该SQL Server实例的名称。

（4）允许远程客户端针对 FILESTREAM 数据启用流访问

选中此控制选项可允许远程客户端访问此服务器上的此 FILESTREAM 数据。

2、修改服务器的属性

打开“SQL Server Management Studio”，修改该实例的配置。默认配置如下：

上述选项解释如下：

（1）“FILESTREAM 访问级别”显示 SQL Server 实例上支持的 FILESTREAM 的当前级别。若要更改访问级别，请选择以下值之一：

已禁用

无法将二进制大型对象 (BLOB) 数据存储在文件系统中。此为默认值。即filestream access level=0

已启用 Transact-SQL 访问

可使用 Transact-SQL 访问 FILESTREAM 数据，但不能通过文件系统进行访问。即filestream access level=1

已启用完全访问

FILESTREAM 数据可使用 Transact-SQL 以及通过文件系统进行访问。即filestream access level=0

注意：在首次启用 FILESTREAM 时，您可能需要重新启动计算机才能配置驱动程序。

（2）“FILESTREAM 共享名称”显示在安装过程中选择的 FILESTREAM 共享的只读名称。

在本次实验中，我们将“FILESTREAM 访问级别” 设定为：已启用完全访问。

如果是通过T-SQL脚本执行，则运行以下脚本：

EXEC syssp_configure N'filestream access level', N'2'

RECONFIGURE WITH OVERRIDE

注意：设置完成之后，重启实例。

3、配置防火墙

若要在防火墙保护的环境中使用 FILESTREAM，客户端和服务器都必须能够将 DNS 名称解析为包含 FILESTREAM 文件的服务器。FILESTREAM 要求 Windows 文件共享端口 139 和 445 处于打开状态。

二、配置文件组

1、添加文件组

完成上述 *** 作之后，就可以为该数据库添加专用于FileStream的文件组。

如果是通过脚本 *** 作，请运行以下脚本：

ALTER DATABASE [db01] ADD FILEGROUP [FileStreamFileGroup] CONTAINS FILESTREAM

2、添加文件

完成上述 *** 作之后，就可以为该数据库添加FilStream类型的数据库文件。

在本例中，系统并没有为“FileStream数据”的文件类型创建mdf或ndf文件，而是在文件夹C:\SqlData下面自动创建以逻辑名称命名的文件夹，即 C:\SqlData\FileData。其中filestreamhdr 文件是 FILESTREAM 容器的头文件。当这个数据库被删除时，mdf、ndf、log连同这个文件夹都会被删除。

如果是通过脚本 *** 作，请运行以下脚本：

ALTER DATABASE [db01] ADD FILE ( NAME = N'FileData', FILENAME = N'C:\SqlData\FileData' ) TO FILEGROUP [FileStreamFileGroup]

注意：在上例中，在运行脚本之前，必须存在C:\SqlData，建议使用右侧的选择按钮选择路径。如果路径不存在，就会报错：

同时不能存在重复的文件夹，即不能存在C:\SqlData\FileData。否则也会报错：

3、启动非事务访问

FileTable 使 Windows 应用程序可以获取 FILESTREAM 数据的 Windows 文件句柄而不需要 SQL Server 事务。为了允许对 SQL Server 中存储的文件进行此非事务性访问，必须为要包含 FileTable 的每个数据库在数据库级别上指定所需的非事务性访问级别。

选项解释如下：

（1）FILESTREAM 非事务访问

为从文件系统到 FileTables 中存储的 FILESTREAM 数据的非事务性访问指定以下选项之一：OFF、READ_ONLY 或 FULL。

如果在服务器上未启用 FILESTREAM，则该值将设置为 OFF 并且被禁用。在本次实验中，将其设置为FULL。

（2）FILESTREAM 目录名称

为与所选数据库相关联的 FILESTREAM 数据指定目录名称。在 FileTable 文件夹层次结构中，此数据库级目录将成为在实例级别为 FILESTREAM 指定的共享名称的子级以及在数据库中创建的 FileTable 的父级。

如果启用非事务性访问时没有提供目录名称，则在以后必须提供它，这样才能在数据库中创建 FileTable。

如果是通过脚本执行，如下：

ALTER DATABASE db01

SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'ImageFiles' )

注意：更改现有数据库时，调用带 DIRECTORY_NAME FILESTREAM 选项的 ALTER DATABASE (Transact-SQL) 语句。使用这些选项更改目录名称时，数据库必须以独占方式锁定，没有打开的文件句柄。

说明：为检查是否在数据库上启用了非事务性访问，可以查询目录视图，脚本如下：

SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc

FROM sysdatabase_filestream_options

三、创建FileTable

1、创建第一个FileTable

“SQL Server Management Studio”只提供一个脚本模板，要想创建FileTable还是得用脚本完成：

USE db01

CREATE TABLE ImageTable1 AS FILETABLE

官方的范本为：

USE [db01]

CREATE TABLE [dbo][ImageTable1] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FileStreamFileGroup]

WITH

(FILETABLE_DIRECTORY = N'ImageTable1', FILETABLE_COLLATE_FILENAME = Chinese_PRC_CI_AS)

2、创建第二个FileTable

CREATE TABLE ImageTable2 AS FILETABLE

3、获取共享路径

文件表创建之后，就会相应的产生一个文件表共享目录，该目录路径可以通过内建函数获取：

SELECT FileTableRootPath('ImageTable1')

本次实验所返回的结果为：\\SQL1\SqlFile\ImageFiles\ImageTable1

4、查看

通过Windows资源管理器，可见已经创建了以GUID命名的文件夹。

通过SQL Server Management Studio，查看表的结构。

四、 *** 作

1、向文件夹中添加文件

通过 “Windows资源管理器”，向文件夹\\SQL1\SqlFile\ImageFiles\ImageTable1中添加一个文件A01GIF。然后运行脚本：

select from ImageTable1

结果如下：

可见， SQL Server自动在Table中添加了记录。

2、文件改名

运行以下脚本：

update ImageTable1 set name='CupGIF' where name='A01GIF'

通过“Windows资源管理器”，查看文件夹\\SQL1\SqlFile\ImageFiles\ImageTable1，可见文件A01GIF已经被改名为CupGIF 。

3、查看共享文件夹

我们可以继续复制其他文件，然后通过“Windows资源管理器”，查看文件夹。

4、删除文件

可以使用脚本删除，例如：

Delete ImageTable1 where name='CupGIF'

或者，通过“Windows资源管理器”直接删除该文件。

五、备份和还原

1、备份数据库

使用 SQL Server 备份数据库时，FILESTREAM 数据将与数据库中的结构化数据一起备份。

2、部分备份

如果不想将 FILESTREAM 数据与关系数据一起备份，则可以使用部分备份将 FILESTREAM 文件组排除在外。

以上就是关于如何在mysql 的存储过程中使用事务全部的内容，包括:如何在mysql 的存储过程中使用事务、如何利用SQL Server 2012数据库 *** 作事务管理、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！