MySql数据库学习笔记(3)

查看默认事务隔离级别

• mysql> select @@tx_isolation;
• mysql> select @@global.tx_isolation;
• mysql> select @@session.tx_isolation;

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

查看默认事务提交方式

mysql> select @@autocommit;

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

修改当前会话事务隔离级别

修改局部

mysql> set tx_isolation = 'Read-uncommitted';\修改局部
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

mysql>  select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

修改全局

mysql> set @@global.tx_isolation= 'Read-uncommitted';\修改全局
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+

mysql> delete from tbl_name where name =888;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

MySQL事物处理实例

MYSQL的事务处理主要有两种方法

1. 用begin,rollback,commit来实现

• begin开始一个事务
• rollback事务回滚
• commit 事务确认

2. 直接用set来改变mysql的自动提交模式
   mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过

• set autocommit = 0 禁止自动提交
• set autocommit = 1 开启自动提交

注意：
当用set autocommit = 0 时，你以后所有的sql都将作为事务处理，
直到你用commit确认或rollback结束；当你结束这个事务的同时也开启了新的事务！
按第一种方法只将当前的做为一个事务!