Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts
0
DatabaseCleaner上面的example过时了。按照示例去做的话,会报错 unknown method 'before'。在github上找到了答案,还是比较简单的。
配置完成以后,就可以在MYSQL去monitor query,看具体是如何操作数据库的。
观察log
最后,重置改动。
class ActiveSupport::TestCase
include FactoryGirl::Syntax::Methods
ActiveRecord::Migration.check_pending!
DatabaseCleaner.strategy = :truncation
DatabaseCleaner.logger = Rails.logger
setup { DatabaseCleaner.start }
teardown { DatabaseCleaner.clean }
end
配置完成以后,就可以在MYSQL去monitor query,看具体是如何操作数据库的。
mysql> SHOW VARIABLES LIKE "general_log%";
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | OFF |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
mysql> SET GLOBAL general_log = 'ON';
观察log
tail -f -n300 /var/run/mysqld/mysqld.log
最后,重置改动。
mysql> SET GLOBAL general_log = 'OFF';
- https://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries
- https://github.com/metaskills/minitest-spec-rails/issues/44#issuecomment-244155657
0
在RR级别下,除了常规的RECORD LOCK,还有一个GAP LOCK。即两条记录之前的间隙。这样的话,就不会允许在范围内插入数据了。http://blog.csdn.net/wanghai__/article/details/7067118 这里有个很好的例子去模拟死锁。
至于分析锁,首先执行
然后,再执行
-- session 1
mysql> start transaction;
mysql> delete from game_summaries where game_id = 2;
-- session 2
mysql> start transaction;
mysql> delete from game_summaries where game_id = 3;
-- session 1
mysql> insert into game_summaries(game_id, score) values (2, 0);
-- waiting
-- session 2
mysql> insert into game_summaries(game_id, score) values(3, 0);
-- deadlock occurs
Deadlock info
然后就按照下面两篇文章去分析锁就行了。
Posted on
Monday, January 22, 2018
by
醉·醉·鱼
and labeled under
Mysql
之前分析过SQL SERVER的死锁,但基本都是基于READ COMMITTED下的死锁。玩得高级点的,就是key lookup lock。最近不幸玩了MySQL,拿原来的理解去尝试分析,结果不对,然后才发现,MySQL的默认隔离级别是REPEATABLE READ。呵呵~在RR级别下,除了常规的RECORD LOCK,还有一个GAP LOCK。即两条记录之前的间隙。这样的话,就不会允许在范围内插入数据了。http://blog.csdn.net/wanghai__/article/details/7067118 这里有个很好的例子去模拟死锁。
至于分析锁,首先执行
set global innodb_status_output_locks=on;
然后,再执行
SHOW ENGINE INNODB STATUS \G
就可以拿到所有session的锁了。-- session 1
mysql> start transaction;
mysql> delete from game_summaries where game_id = 2;
-- session 2
mysql> start transaction;
mysql> delete from game_summaries where game_id = 3;
-- session 1
mysql> insert into game_summaries(game_id, score) values (2, 0);
-- waiting
-- session 2
mysql> insert into game_summaries(game_id, score) values(3, 0);
-- deadlock occurs
Deadlock info
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-11 02:19:51 0x7ff5b7b83700
*** (1) TRANSACTION:
TRANSACTION 365986, ACTIVE 59 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140693325752064, query id 1184 172.18.0.1 root update
insert into game_summaries(game_id, score) values (2, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365986 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 365987, ACTIVE 45 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17, OS thread handle 140693326018304, query id 1186 172.18.0.1 root update
insert into game_summaries(game_id, score) values(3, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000009; asc ;;
1: len 4; hex 80000002; asc ;;
*** WE ROLL BACK TRANSACTION (2)
然后就按照下面两篇文章去分析锁就行了。
- http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/
- http://keithlan.github.io/2017/06/05/innodb_locks_1/
如果你有SQL SERVER的背景知识,简单来说,就是基本的record lock(以及相关的index),加上gap lock。一旦有gap lock,这个范围内是不允许插入数据的。这就增加了死锁发生的几率。这种情况更多是发生在DELETE & INSERT 组合情况下。
在上面的例子里面,两个delete statement所加的gap lock是不会相互冲突的。但是会阻止后续的插入。
0
安装mysql的命令太多,记下以备后用,免得再去寻找。
- 安装
sudo apt-get install mysql-server
安装完成以后,默认会自动启动mysql。如果没有启动mysql,继续下面的方法。 - 启动,停止,重启
sudo start mysql
sudo stop mysql
sudo restart mysql - 登陆
mysql –h hostname –u username –p
hostname为远程机器IP,如果是本机,直接mysql –u username -p即可。 - 更改权限/创建新用户
grant all privileges on *.* to ‘username’@'%’ indentified by ‘password’ with grant option;
更新username为所有的权限,%是指该用户可以在所有的ipaddress上面通过password访问mysql。
如果该用户名不存在,则会创建一个新的用户。
如果希望用户在指定机器上访问,则将%替换为改机器的address。 - 提交更新
flush privileges; - 开放远程登陆
默认情况下,mysql只允许本机访问,需要修改/etc/mysql/my.cnf文件,将bind-address = 127.0.0.1 注释掉。同时,还需要确认该用户有远程登陆的权限,即#4里面,@后面需要使用%。
重启mysql。 - 操作数据库 > 显示数据库列表
show databases; - 操作数据库 > 显示数据表
use mysql;
show tables; - 操作数据库 > 显示数据库结构
describe {table name}; - 操作数据库 > 建库与删库
create database {database name};
drop database {database name}; - 操作数据库 > 建表
use database;
create table {table name};
drop table {table name}; - 操作数据库 > 删除,更新,插入,查询