1、Innodb是一种事务性存储引擎

2、完全支持事务的ACID特性

3、实现事务特性的原理：

    使用Redo Log和Undo Log，Undo Log用于帮助未提交事务进行回滚，Redo Log记录

    已经提交的事务，Undo Log会随机读写，而Redo Log基本是顺序

4、Innodb支持的是行级锁，在进行写操作时需要的资源更少，支持的并发更多

5、行级锁是由存储引擎层实现的

6、锁：

    锁的主要租用是管理共享资源的并发访问

    锁用于实现事务的隔离性

    共享锁（读锁）

    独占锁（写锁）

    锁的粒度：根据粒度分为表级锁，行级锁，粒度越大，并发就越小

7、阻塞和死锁：

    阻塞：一个事务中的锁需要等待另一个事务中的锁释放，形成的是阻塞

    死锁：两个或两个以上的事务在执行中相互占用了对方的资源

8、Innodb状态检查：

    show engine innodb status;

---------------------

原文：https://blog.csdn.net/qq_28893679/article/details/78283102

mariadb> show engines;

+--------------------+---------+----------------------------------------------------------------------------+--------------+-----+------------+

| Engine             | Support | Comment                                                                    | Transactions | XA  | Savepoints |

+--------------------+---------+----------------------------------------------------------------------------+--------------+-----+------------+

| CSV                | YES     | CSV storage engine                                                         | NO           | NO  | NO         |

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO  | NO         |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO  | NO         |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO  | NO         |

| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO  | NO         |

| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES | YES        |

| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO  | NO         |

| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO  | YES        |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO  | NO         |

| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO  | NO         |

+--------------------+---------+----------------------------------------------------------------------------+--------------+-----+------------+

10 rows in set

#最常用的是innodb和MyISAM

#可见，默认引擎是innodb

mariadb> show create table history;

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                                                                                                                                                                                                         |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| history | CREATE TABLE `history` (

  `itemid` bigint(20) unsigned NOT NULL,

  `clock` int(11) NOT NULL DEFAULT '0',

  `value` double(16,4) NOT NULL DEFAULT '0.0000',

  `ns` int(11) NOT NULL DEFAULT '0',

  KEY `history_1` (`itemid`,`clock`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql> use config;

Database changed

mysql> create table size_test engine=myisam as select * from SvrConfig;

Query OK, 672 rows affected (0.01 sec)

Records: 672  Duplicates: 0  Warnings: 0

mysql> use information_schema

Database changed

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB')as data from TABLES where table_schema='config' and table_name='size_test';

+--------+

| data   |

+--------+

| 0.13MB |

+--------+

1 row in set (0.00 sec)

mysql> alter table config.size_test engine=innodb;

Query OK, 672 rows affected (0.06 sec)

Records: 672  Duplicates: 0  Warnings: 0

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB')as data from TABLES where table_schema='config' and table_name='size_test';

+--------+

| data   |

+--------+

| 0.20MB |

+--------+

1 row in set (0.00 sec)

SELECT `id` FROM  table WHERE　id in(1,2)   LOCK IN SHARE MODE

#结果集的数据都会加共享锁

<?php

        $uid=$_SESSION['uid'];

        //开启事务

        sql:begin

        //开启行级锁的排他锁

        sql:SELECT `coin` FROM user WHERE id=$uid FOR UPDATE

        //扣除用户账户钱币

        $res=update user set coin=coin-value where id=1;

        if($res){

            //将用户的提现信息添加到提现表

            sql:insert into user values(null,"{$uid}",value);

            //判断添加结果

            if(add_cash_result){

                sql:commit

            }else{

                sql:rollback

            }

        }else{

            sql:rollback;

        }