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

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

6.7.1 BEGIN/COMMIT/ROLLBACK句法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ALTER TABLE BEGIN CREATE INDEX

DROP DATABASE DROP TABLE RENAME TABLE

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

6.7.2 LOCK TABLES/UNLOCK TABLES句法

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

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

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

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

SELECT权限。在 MySQL 3.23中，你对该表需要有 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锁。

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

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

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

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

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

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

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

你可以使用 KILL安全地杀死一个正在表锁定的线程。查看章节 4.5.5 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;

不使用 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()实现以获得高速度。查看章节 6.3.6.2

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

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

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

SET [GLOBAL| SESSION] TRANSACTION ISOLATION LEVEL

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

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

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

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

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

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

二、mysql中事务和存储过程的区别

1、通过一系列的SQL语句，根据传入的参数（也可以没有），通过简单的调用，

2、完成比单个SQL语句更复杂的功能，存储在数据库服务器端，只需要编译过一次之后再次使用都不需要再进行编译。主要对存储的过程进行控制。

3、事务是一系列的数据更改操作组成的一个整体。一旦事务中包含的某操作失败或用户中止，用户可以控制将事务体中所有操作撤消，返回事务开始前的状态。

4、事务中的操作是一个整体，要么整体完成，要么全部不做。从而保证了数据的完整性。

5、Mysql中，MyISAM存储引擎不支持事务，InnoDB支持。

6、两者都是数据库中非常重要的知识。

三、关于mysqli和mysql在处理存储过程和事务的区别

1、常说的mysql与mysqli，那是说php方面的函数集，跟mysql数据库没有关系。

2、在php5之前，大家都是用php的mysql去驱动mysql的，如mysql_query()的函数，但这样都是面向过程，算是第一代的mysql数据库驱动程序，也比较原始兼容性好。

3、在php5以后，随着面向对象的加入，就增加mysqli的函数功能，他是原先mysql系统函数的增强版，更稳定更高效更安全，如mysqli_query()。mysqli是面向对象，以对象的方式操作驱动mysql数据库。这个算是第二代了。

4、mysql的函数，一般都有对应一个mysqli的函数，如mysql_query对应mysqli_query，功能一样。当然mysqli也根据自身的特点加入了一些mysql没有的函数。

5、支不支持事务，要看mysql数据库的版本和存储引擎，跟php的版本无关。

6、新软件开发，建议还是用mysqli系列的面象对象方式，性能更好，但要考虑好环境的兼容问题。