当前位置： 首页 > news >正文

网站内容添加/软文广告文案

news 2025/7/23 21:20:53
网站内容添加,软文广告文案,上海自助建站系统,运营培训班mysql版本：5.7目的：在RC下，name列上仅有key索引，并发插入name时不出现重复数据RC不加gap lock，并且复合select语句是不加锁的快照读，导致两个事务同时进行都可插入，测试如下：client1…

mysql版本：5.7

目的：在RC下，name列上仅有key索引，并发插入name时不出现重复数据

RC不加gap lock，并且复合select语句是不加锁的快照读，导致两个事务同时进行都可插入，测试如下：

client1：

mysql> set tx_isolation='read-committed';

mysql> select @@tx_isolation;+----------------+

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set, 1 warning (0.00sec)

mysql> create table t (id int primary key, name int, key(name))engine=innodb;

Query OK,0 rows affected (0.24sec)

.......

mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

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

7 rows in set (0.00sec)

mysql> set autocommit=0;

Query OK,0 rows affected (0.00sec)

mysql> show variables like 'autocommit';+---------------+-------+

| Variable_name | Value |

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

| autocommit | OFF |

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

1 row in set (0.03sec)

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8);

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

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

8 rows in set (0.00 sec)

client2设置同client1，设置略，然后起事务插入：

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8);

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 9 | 8 |

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

8 rows in set (0.00 sec)

可以看到并未阻塞，这不同于RR，在RR下会阻塞，因为加了gap lock。

难道这时候没有加任何锁吗，其实并不是，client1执行如下，并查看锁：

mysql> select name from t where name=8 lock inshare mode;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_schema.innodb_locks;+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

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

| 164163:469:4:10 | 164163 | S | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 9 |

| 164168:469:4:10 | 164168 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 9 |

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

2 rows in set, 1 warning (0.00 sec)

看看加锁的数据。client2如下：

mysql> select name from t where name=8 lock inshare mode;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_schema.innodb_locks;+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

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

| 164168:469:4:9 | 164168 | S | RECORD | `test1`.`t` | name | 469 | 4 | 9 | 8, 8 |

| 164163:469:4:9 | 164163 | X | RECORD | `test1`.`t` | name | 469 | 4 | 9 | 8, 8 |

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

2 rows in set, 1 warning (0.00 sec)

看看加锁的数据，可见client1和2都上了锁，是在insert时上的。

那么为了能达到加锁阻塞的目的，可以使用如下方式，client1：

mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8 for update);

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0

client2则阻塞：

mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8 for update);

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_schema.innodb_locks;+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |

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

| 164170:469:4:10 | 164170 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 8 |

| 164169:469:4:10 | 164169 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 8 |

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

2 rows in set, 1 warning (0.00 sec)

如果client1在client2阻塞时 commit：

mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8 for update);

Query OK,1 rows affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> commit;

Query OK,0 rows affected (0.04 sec)

client2：

mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8 for update);

Query OK,0 rows affected (4.79sec)

Records:0 Duplicates: 0 Warnings: 0

还有一个需要注意的地方是，如果不加for update，则并发插入时，都会插入新数据，client1：

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

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

8 rows in set (0.00sec)

mysql> insert into t select 9,9 from dual where not exists (select name from t where name=9);

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

| 9 | 9 |

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

9 rows in set (0.00sec)

mysql> commit;

Query OK,0 rows affected (0.03sec)

mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

| 9 | 9 |

| 10 | 9 |

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

10 rows in set (0.00 sec)

与client1并发执行的client2：

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

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

8 rows in set (0.00sec)

mysql> insert into t select 10,9 from dual where not exists (select name from t where name=9);

Query OK,1 row affected (0.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

| 10 | 9 |

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

9 rows in set (0.00sec)

mysql> commit;

Query OK,0 rows affected (0.04sec)

mysql> select * fromt;+----+------+

| id | name |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

| 9 | 9 |

| 10 | 9 |

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

10 rows in set (0.00 sec)

可见，根本起不到不存在则插入的效果。

http://www.lbrq.cn/news/1291537.html

相关文章：

  • 重庆工程建设信息网证件查询/长沙网站seo哪家公司好
  • 个体工商户 经营性网站/seo推广技术
  • 蓟县做网站/成都正规搜索引擎优化
  • dw制作简单网站/链接生成器
  • 站群系列服务器做视频网站/企业网站建设的重要性
  • 怎么创网站/如何做到精准客户推广
  • 网站建设实验原理/竞价推广开户
  • 高职两学一做专题网站/搜索引擎广告优化
  • 网站个性化/合肥网站关键词排名
  • 保定网站推广/微信营销典型案例
  • 郑州做网站和域名/5118站长工具
  • wordpress微信登录插件下载失败/seo和sem的区别是什么?
  • 国内做批发的网站有哪些/白杨seo教程
  • wordpress中国可以上吗/沈阳seo排名收费
  • 微享网络网站建设/做一个公司网站要多少钱
  • 网站开发用什么系统比较好/响应式模版移动优化
  • 做的烂的网站/app开发用什么软件
  • 网站建设术语/搜索引擎调词工具
  • 个人网站可以做百度推广么/东莞建设企业网站
  • 怎样制作时时彩网站做 裙 o/个人接外包的网站
  • 潍坊网站制作套餐/腾讯朋友圈广告投放价格
  • 网站建设基本流程是什么/百度网盘官网登陆入口
  • wap网站设计方案/百度词条搜索排行
  • mac可以做网站开发吗/湖北seo推广
  • 怎么做一种网站为别人宣传/西安网络优化哪家好
  • 网站flash动画效果代码/怎么提升关键词的质量度
  • 女人与狗做视频网站/四川游戏seo整站优化
  • 电商网站开发会遇到哪些问题/指数型基金
  • 符合三网标准的网站建设/网络营销咨询公司
  • 小伙做网色网站/b站推广软件
  • 大数据之Hive:Hive中week相关的几个函数
  • [3-02-02].第04节：开发应用 - RequestMapping注解的属性2
  • Python 绘制各类折线图全指南：从基础到进阶
  • 深入解析MIPI C-PHY (二）C-PHY三线魔术：如何用6种“符号舞步”榨干每一滴带宽？
  • 用 STM32 的 SYSTICK 定时器与端口复用重映射玩转嵌入式开发
  • C++ - 仿 RabbitMQ 实现消息队列--服务端核心模块实现（二）