MYSQL_innodb的加锁机制实验
来源:互联网 发布:mac重装系统磁盘解锁 编辑:程序博客网 时间:2024/04/30 13:47
--session 1
mysql> create table test(id int,nick varchar(32)) engine=innodb ;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(3,'c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(4,'d');
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | nick |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> update test set nick='lock_a' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--session 2
mysql> select * from test;
+------+------+
| id | nick |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
mysql> update test set nick='lock_b' where id=2;
Query OK, 1 row affected (1 min 29.90 sec) --等待很长的时间,直到session 1提交为止
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+------+--------+
| id | nick |
+------+--------+
| 1 | lock_a |
| 2 | lock_b |
| 3 | c |
| 4 | d |
+------+--------+
4 rows in set (0.00 sec)
如果在test表的id列上创建索引,再观察一下加锁情况
--session 1
mysql> create unique index uk_test_id on test(id);
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> update test set nick='lock_aa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新后,但并不提交,执行session 2的更新语句,发现立刻执行完
--session 2
mysql> update test set nick='lock_bb' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--session 1,session 2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
- MYSQL_innodb的加锁机制实验
- Sqlite的加锁机制
- mysql_Innodb的undo_log和redo_log
- mysql_Innodb的undo_log和redo_log
- C++中多线程的加锁机制
- C++中多线程的加锁机制
- Python3防止死锁的加锁机制
- 双重检查加锁机制
- mysql加锁机制
- Linux加锁机制整理
- Insert into 加锁机制
- Java加锁机制
- insert into 加锁机制
- lucene的加锁机制和LockObtainFailedException的异常
- Lucene的多线程访问原则和同步,加锁机制
- Mysql事务以及加锁机制事务的特征ACID
- python的互斥锁,加锁、同步机制、异步通信
- Mysql事务以及加锁机制事务的特征ACID
- linux下获取本机IP
- android工程中引入第三方JAR包后安装APK时老是提示找不到库文件 || Android如何将程序打成jar包 || 运行java.lang.noclassdeffounderror错误
- MySQL的锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
- ZigBee无线协议学习笔记(3)
- 应该掌握的几个HTML标记语言(个人总结)
- MYSQL_innodb的加锁机制实验
- ssh 发送邮件
- Android SystemProperties 系统属性分析
- 开源DBCP、C3P0、Proxool 、 BoneCP连接池的比较
- mysql InnoDB行锁
- 如何删除项目中的.svn文件夹
- Decorator 装饰者模式
- 轻松学习正则表达式
- 文件大小和占用空间