查看默认事务隔离级别
- 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的事务处理主要有两种方法
- 用begin,rollback,commit来实现
- begin开始一个事务
- rollback事务回滚
- commit 事务确认
- 直接用set来改变mysql的自动提交模式
mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
- set autocommit = 0 禁止自动提交
- set autocommit = 1 开启自动提交
注意：
当用set autocommit = 0 时，你以后所有的sql都将作为事务处理，
直到你用commit确认或rollback结束；当你结束这个事务的同时也开启了新的事务！
按第一种方法只将当前的做为一个事务!