事物开始方式:

start transaction
**

开启只读事物

[READ ONLY] 只读事物: 在这个事物里是不能对数据库发生修改的
start transaction read only;
该事物中不允许修改数据

  1. mysql> start transaction read only;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select * from t1;
  5. +------+
  6. | i    |
  7. +------+
  8. |    1 |
  9. |    2 |
  10. +------+
  11. 2 rows in set (0.00 sec)
  13. mysql> delete from t1 where i=1;
  14. ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

特点: 不分配回滚段, 在innodb_trx中不会显示即不仅如此事物队列
如果是只读事物 show engine innodb status 的事物列表中的transaction id 是非常大的 ,不分配回滚段 undo log entries .可写事物则transaction id 相对会小很多如下图: 且会分配 undo log entries 的(该特性在5.7 中有,5.6.10 测试中发现不会有这种变化 ,trx_id =6821471(都是这么多为) )

5.7 测试只读事物由读转变成带写操作的trx_id变化

一下操作S1 都在一个事物里进行
S1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 (start transaction read only;
  start transaction READ WRITE;
  都一样只是由于后面需要dml因此使用了start transaction命令)
S2
mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

mysql> select * from t1 limit 1;
+----+------+--------+
| id | name | hostna |
+----+------+--------+
|  1 | pp   | dd     |
+----+------+--------+
1 row in set (0.00 sec)
S2
mysql> select * from information_schema.innodb_trx;
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 422177109706576 | RUNNING   | 2019-08-23 15:54:09 | NULL                  | NULL             |          0 |              335892 | NULL      | NULL                |                 0 |                 0 |                0 |                  1136 |               0 |                 0 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)
S1
mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.01 sec)
S2
mysql> select * from information_schema.innodb_trx;
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 225055 | RUNNING   | 2019-08-23 15:54:09 | NULL                  | NULL             |          3 |              335892 | NULL      | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | READ COMMITTED      |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)

S1
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
S2
mysql> select * from information_schema.innodb_trx;
Empty set (0.00 sec)

读写事物

[READ WRITE] :读写模式,可读可写 默认的

WITH CONSITENCE SNAPSHOP:开启一致性快照
start transaction with consitence snapshop;

事物提交

显示提交:

commit
隐式提交会将当前会话之前未提交的事物进行提交
隐式提交的方式:
begin/begin work
start transaction;
set autocommit=1
DDL/DCL语句
DCL: grant create user 等SQL
set password
revock

回滚 事物

显示回滚:

ROLLBACK
隐式回滚
1.连接断开
2.超时断开
3被kill
4异常宕机(指未提交的事物)

自动提交的意义

设置开启自动提交: set autocommit=1

关闭的作用:
比如在批量导入的时候可以设置非自动提交,批量做commit
因为: 只有在提交的时候才会刷undo redo 和日志还有刷数据文件, 这样不会频繁创建undo 和redo
(小知识: 关闭了 unique_check参数,但是如果有唯一索引,当插入重复值依然会报错.dump导入数据是依然会报错)

关闭自动提交可能出现的问题:
忘记提交,锁不释放
另一个事物长期所等待,严重影响tps