[Todo] 乐观悲观锁，自旋互斥锁等等
2024-10-19 02:31:14
乐观锁、悲观锁、要实践
http://chenzhou123520.iteye.com/blog/1860954 《mysql悲观锁总结和实践》
http://chenzhou123520.iteye.com/blog/1863407 《mysql乐观锁总结和实践》
http://outofmemory.cn/sql/optimistic-lock-and-pessimistic-lock
注意，以下的表里面的列名，一定要用 `` 反引号来包括。
mysql> create table `t_goods` (
-> `id` bigint(11) NOT NULL AUTO_INCREMENT,
-> `status` bigint(11) DEFAULT 0,
-> `name` varchar(32) DEFAULT NULL,
-> `version` bigint(11) DEFAULT 1,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into t_goods (`name`) values ('weapon');
Query OK, 1 row affected (0.07 sec)
mysql> insert into t_goods (`name`) values ('equipment');
Query OK, 1 row affected (0.10 sec)
mysql> select * from t_goods;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 1 | 0 | weapon | 1 |
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
2 rows in set (0.00 sec)
实验1，select for update 指定主键，只锁行：
首先要关闭autocommit：
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
如果不关闭，经过实验，的确不会相互影响。
关闭autocommit之后，普通的sql不放在事务里面也可以。
console A: mysql> select * from t_goods where id = 1 for update;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 0 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) console B:
mysql> select * from t_goods where id = 1;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 0 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) mysql> select * from t_goods where id = 2 for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
1 row in set (0.00 sec) mysql> select * from t_goods where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以看出，不加for update不影响，加了for update不是同一行，不影响（仅对于主键查询有关）。
实验2，select for update 指定非主键，锁全部：
Console A:
mysql> select * from t_goods where name = 'weapon' for update;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 0 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) Console B:
mysql> select * from t_goods;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 1 | 0 | weapon | 1 |
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
2 rows in set (0.00 sec) mysql> select * from t_goods where name = 'equipment' for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from t_goods where id = 2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
实验3，查询主键没查到，不锁：
Console A:
mysql> select * from t_goods where id=3 for update;
Empty set (0.01 sec) Console B:
mysql> select * from t_goods for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 1 | 0 | weapon | 1 |
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
2 rows in set (0.00 sec)
说明，主键没查到数据，不加锁。
实验4，查询非主键，没查到，锁全部，table lock.
Console A:
mysql> select * from t_goods where name = 'abc' for update;
Empty set (0.00 sec) Console B:
mysql> select * from t_goods for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from t_goods where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
实验5：查询主键不明确，为范围，大于小于，只锁相关的行；
Console A:
mysql> select * from t_goods where id > 1 for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
1 row in set (0.00 sec) Console B:
mysql> select * from t_goods where id = 1 for update;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 0 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) mysql> select * from t_goods where id = 2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
实验5：查询主键不明确，!=或者<>，锁全部；
Console A:
mysql> select * from t_goods where id != 1 for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
1 row in set (0.00 sec) Console B:
mysql> select * from t_goods where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction Console A:
mysql> select * from t_goods where id <> 1 for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
1 row in set (0.00 sec) Console B:
mysql> select * from t_goods where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
实验6，对于普通索引，也类似于主键的效果：
未加索引之前，锁全表：
console A:
mysql> select * from t_goods where status = 1 for update;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 1 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) console B:
mysql> select * from t_goods where id = 2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 加了索引之后，只锁行：
console A:
mysql> alter table t_goods add index index_name(`status`);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t_goods where status = 1 for update;
+----+--------+--------+---------+
| id | status | name | version |
+----+--------+--------+---------+
| 1 | 1 | weapon | 1 |
+----+--------+--------+---------+
1 row in set (0.00 sec) console B:
mysql> select * from t_goods where id = 2 for update;
+----+--------+-----------+---------+
| id | status | name | version |
+----+--------+-----------+---------+
| 2 | 0 | equipment | 1 |
+----+--------+-----------+---------+
1 row in set (0.00 sec) mysql> select * from t_goods where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
乐观锁：
从业务层面加锁，一般是加上version字段，然后sql以如下形式处理：
update t_goods
set status=2,version=version+1
where id=#{id} and version=#{version};
自旋锁与互斥锁
http://blog.csdn.net/a675311/article/details/49096435
自旋锁就是不听的忙检测，拿不到锁就返回。
pthread中提供的锁有：pthread_mutex_t, pthread_spinlock_t, pthread_rwlock_t。 pthread_mutex_t是互斥锁，同一瞬间只能有一个线程能够获取锁，其他线程在等待获取锁的时候会进入休眠状态。因此pthread_mutex_t消耗的CPU资源很小，但是性能不高，因为会引起线程切换。
pthread_spinlock_t是自旋锁，同一瞬间也只能有一个线程能够获取锁，不同的是，其他线程在等待获取锁的过程中并不进入睡眠状态，而是在CPU上进入“自旋”等待。自旋锁的性能很高，但是只适合对很小的代码段加锁（或短期持有的锁），自旋锁对CPU的占用相对较高。
pthread_rwlock_t是读写锁，同时可以有多个线程获得读锁，同时只允许有一个线程获得写锁。其他线程在等待锁的时候同样会进入睡眠。读写锁在互斥锁的基础上，允许多个线程“读”，在某些场景下能提高性能。
诸如pthread中的pthread_cond_t, pthread_barrier_t, semaphone等，更像是一种同步原语，不属于单纯的锁。
http://www.cnblogs.com/hdflzh/p/3716156.html
http://blog.csdn.net/pi9nc/article/details/39177343
Java锁相关
http://blog.csdn.net/Evankaka/article/details/44153709 (这一篇要重点看，讲了Thread Runnable等)
http://blog.csdn.net/Evankaka/article/details/51866242（Java锁技术内幕上）
http://blog.csdn.net/evankaka/article/details/51932044（Java锁技术内幕中）
最新文章
- AdaBoost
- Visual Studio VS2010统计代码行数(转载)
- hibernateTemplate.find或hibernateTemplate.save()执行操作没有反应，但是有sql语句
- Android中四种OnClick事件的写法
- 国产单机RPG游戏的情怀
- webbench的详细使用
- 查看wtmp文件内容
- WinDbg配置和使用基础
- Linux安装make无法使用
- js中__proto__和prototype的区别和关系？
- 【转】Keberos认证原理
- javascript 之数据类型
- 【dfs】p1451 求细胞数量
- Volley源码分析(三)NetWorkDispatcher分析
- SonarQube 平台搭建代码审查平台步骤
- Visual SVN的安装
- http中的get和post的区别
- Day 5 字典的操作
- Linux-(ls,mv,mkdir,rm,cp)
- UNIX高级环境编程（8）进程环境（Process Environment）- 进程的启动和退出、内存布局、环境变量列表