MySQL一些零散记录

发表于

MySQL 大小写区分问题

sql_mode 配置

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

查看当前sql_mode

1
2
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

设置当前sql_mode

1
2
SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';

Full list of SQL Models

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

/*!40001 SQL_NO_CACHE */

/*! */ 这是 mysql 里的语法，并非注释，! 后面是版本号，如果本数据库等于或大于此版本号，那么注释内的代码也会执行。

关于这个条件的问答： https://lists.mysql.com/mysql/203373

MySQL 锁表种类

常见的有行锁和表锁。表锁会锁住整张表，并发能力弱，开发中要避免使用表级锁。行锁只将单行数据锁住，锁数据期间对其它行数据不影响，并发能力高，一般使用行锁来处理并发事务。
MySQL是如何加不同类型的锁的？对于加锁数据的筛选条件，有其对应的索引建立，MySQL可以快速定位的数据进行行级加锁；而对于没有索引的情况，MySQL 的做法是会先锁住整张表，然后再去获取数据，然后将不满足条件的数据锁释放掉。

等待锁超时问题

Lock wait timeout exceeded; try restarting transaction
一种情况是因为有操作语句对整个表加锁了，这里发现的例子是在开启事务做 UPDATE 更新时发现的，UPDATE 条件如果不是主键或者没有索引则会锁整张表，只有以主键为条件或完全匹配的唯一索引做更新才是行级锁。
还有就是另一个事务中持有锁时间过长导致。

1
2
3
4
5
6
SELECT * FROM INNODB_TRX;  // 查看事务表锁状态

// 创建事务，更新语句，但是不提交
SET SESSION AUTOCOMMIT=off;
BEGIN;
UPDATE tabl1 SET status=1 WHERE expired_at <123456 AND expired_at >= 12346 AND `status` = 0;

这时候再去提交则会报等待锁超时问题。

http://www.toniz.net/?p=556

加行锁的注意事项：

http://blog.csdn.net/u014453898/article/details/56068841

插入语句死锁问题

在 INSERT 语句中出现 Deadlock found when trying to get lock; try restarting transaction 是因为范围匹配加锁是对索引页加锁了，导致其它事务插入数据时报死锁。处理办法是查询改成行锁，以 ID 或唯一索引加锁。

这里需要强调的是尽量避免使用范围加锁。最好是通过主键加行锁处理。

避免加锁失败和发生死锁的注意事项

  1. 减少锁占用时间，避免拿锁时做过多耗时操作。
  2. 加锁条件需对应加索引，尽量为行级锁。
  3. 避免死锁需要再开启事务后一次将所需资源加锁，处理后及时 COMMIT 释放锁。
  4. 对于请求的网络资源，首先将所需外部资源准备好。
  • 对于开启事物后加锁，只有 COMMIT 后方可释放锁
  • 在捕获异常中的处理，在捕获异常后要记得 ROLLBACK
  • 等待锁超时时间一般设置在 1-2 秒时间 SET innodb_lock_wait_timeout=1。

MySQL 事务

http://blog.csdn.net/zhaoyangjian724/article/details/52858519

先非主键范围加锁
查看事务状态
插入区分度高的数据-成功
插入区分度低的数据-失败
查看引擎状态，发现页锁
改为以主键做查询条件加锁
插入区分度低的数据-成功

SELECT * FROM INNODB_TRX;

SHOW ENGINE INNODB STATUS;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

=====================================
2017-12-18 11:47:27 7fd5dd6f6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 57 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 22630 srv_active, 0 srv_shutdown, 15047550 srv_idle
srv_master_thread log flush and writes: 15067795
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21761
OS WAIT ARRAY INFO: signal count 42765
Mutex spin waits 38129, rounds 296291, OS waits 3638
RW-shared spins 25778, rounds 578697, OS waits 16634
RW-excl spins 4632, rounds 145148, OS waits 1184
Spin rounds per wait: 7.77 mutex, 22.45 RW-shared, 31.34 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 167458
Purge done for trx's n:o < 167444 undo n:o < 0 state: running but idle
History list length 942
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 2700260, OS thread handle 0x7fd5dd6f6700, query id 16186792 10.21.0.2 wallet init
SHOW ENGINE INNODB STATUS
---TRANSACTION 167433, not started
MySQL thread id 2700100, OS thread handle 0x7fd5dd7fa700, query id 16185344 10.100.27.2 wallet cleaning up
---TRANSACTION 167449, not started
MySQL thread id 2700073, OS thread handle 0x7fd5e6581700, query id 16186116 10.100.53.2 wallet cleaning up
---TRANSACTION 167434, not started
MySQL thread id 2700060, OS thread handle 0x7fd5e6685700, query id 16185479 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699993, OS thread handle 0x7fd5dd5f2700, query id 16184159 10.100.27.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699968, OS thread handle 0x7fd5dd633700, query id 16183926 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699967, OS thread handle 0x7fd5dd7b9700, query id 16183863 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699966, OS thread handle 0x7fd5e6540700, query id 16183754 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699965, OS thread handle 0x7fd5e4049700, query id 16183668 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699964, OS thread handle 0x7fd5e66c6700, query id 16183581 10.100.53.2 wallet cleaning up
---TRANSACTION 0, not started
MySQL thread id 2699963, OS thread handle 0x7fd5dd6b5700, query id 16183494 10.100.53.2 wallet cleaning up
---TRANSACTION 167457, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 2700275, OS thread handle 0x7fd5dd737700, query id 16186778 10.21.0.2 wallet update
INSERT INTO `ttt` (`id`, `no`, `trade_number`)
VALUES
	(586, '856195904590458889', '200526175912156728')
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 5 n bits 96 index `idx_number` of table `test`.`ttt` trx id 167457 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 29; hex 5a484c4956454255593230313730353236323233323432363434353939; asc ZHLIVEBUY20170526223242644599;;
 1: len 8; hex 0000000000000229; asc        );;

------------------
---TRANSACTION 167447, ACTIVE 602 sec
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 2699383, OS thread handle 0x7fd5e4251700, query id 16186751 10.21.0.2 wallet cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6707 OS file reads, 383968 OS file writes, 119945 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.11 writes/s, 0.09 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 74, seg size 76, 733 merges
merged operations:
 insert 34, delete mark 169019, delete 2
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 149489, node heap has 168 buffer(s)
0.00 hash searches/s, 0.05 non-hash searches/s
---
LOG
---
Log sequence number 394355624
Log flushed up to   394355624
Pages flushed up to 394355624
Last checkpoint at  394355624
0 pending log writes, 0 pending chkp writes
65750 log i/o's done, 0.04 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 77266944; in additional pool allocated 0
Dictionary memory allocated 669112
Buffer pool size   4607
Free buffers       1024
Database pages     3415
Old database pages 1240
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11699, not young 475003
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6606, created 13587, written 300725
0.00 reads/s, 0.00 creates/s, 0.05 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3415, unzip_LRU len: 0
I/O sum[3]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 12657, id 140556665968384, state: sleeping
Number of rows inserted 227014, updated 19739, deleted 89828, read 47831814
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================