mysql> select sn,id,info from s100 group by id;
+-----+------+------+
| sn | id | info |
+-----+------+------+
| 227 | 1 | aa |
| 228 | 2 | bb |
+-----+------+------+
2 rows in set (0.02 sec)
mysql> select sn,count(id),info from s100 group by id;
+-----+-----------+------+
| sn | count(id) | info |
+-----+-----------+------+
| 227 | 32768 | aa |
| 228 | 1 | bb |
+-----+-----------+------+
2 rows in set (0.02 sec)
mysql>
mysql>
mysql> show index from s100;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| s100 | 0 | PRIMARY | 1 | sn | A | 33366 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show variables like '%tx_isolation%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | OFF |
| binlog_order_commits | ON |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
5 rows in set (0.00 sec)
Session 1:
mysql> select * from s100 where id=2 for update;
+-----+------+------+
| sn | id | info |
+-----+------+------+
| 228 | 2 | bb |
+-----+------+------+
1 row in set (0.02 sec)
Session 2:
Database changed
mysql> insert into s100(id,info) values(9999,'fcadfad');
Query OK, 1 row affected (0.00 sec)
RC模式下,是行锁 不会上升到表级锁
