mysqltrx

Ⅰ 怎麼統計mysql中有多少個死鎖

1，查看資料庫的隔離級別：
mysql> select @@tx_isolation;
2，去查看先當前庫的線程情況：
mysql> show processlist;
沒有看到正在執行的慢SQL記錄線程，再去查看innodb的事務表INNODB_TRX，看下裡面是否有正在鎖定的事務線程，看看ID是否在show full processlist裡面的sleep線程中，如果是，就證明這個sleep的線程事務一直沒有commit或者rollback而是卡住了，我們需要手動kill掉。
mysql> SELECT * FROM information_schema.INNODB_TRX;
如果有記錄,則找到trx_mysql_thread_id這個欄位對應的id, 將其kill掉。假如id=100
mysql->kill 100
SELECT CONCAT_WS('','kill',' ',t.trx_mysql_thread_id,';')a FROM information_schema.INNODB_TRX t;
4，總結分析
表數據量也不大，按照普通的情況來說，簡單的update應該不會造成阻塞的，mysql都是autocommit，不會出現update卡住的情況，去查看下autocommit的值。
mysql> select @@autocommit;
1表示自動提交。0表示不自動提交。
如果你發現自己的資料庫autocommit=0，將它改正吧。
解除死鎖的兩種方法：
（1）終止（或撤銷）進程。終止（或撤銷）系統中的一個或多個死鎖進程，直至打破循環環路，使系統從死鎖狀態中解除出來。
（2）搶占資源。從一個或多個進程中搶占足夠數量的資源，分配給死鎖進程，以打破死鎖狀態。

Ⅱ mysql資料庫鎖表如何解鎖

當前運行的所有事務
select * from information_schema.innodb_trx
當前出現的鎖
select * from information_schema.innodb_locks
鎖等待的對應關系
select * from information_schema.innodb_lock_waits
通過找到線程id號，進行kill

通過 select * from information_schema.innodb_trx 查詢 trx_mysql_thread_id然後執行 kill 線程ID

Ⅲ 怎麼看mysql有沒阻塞

通過下面的查詢，來查詢當前資料庫，有哪些事務，都鎖定哪些資源。

SELECT
	trx_idAS`事務ID`,
	trx_stateAS`事務狀態`,
	trx_requested_lock_idAS`事務需要等待的資源`,
	trx_wait_started	AS`事務開始等待時間`,
	trx_tables_in_useAS`事務使用表`,
	trx_tables_lockedAS`事務擁有鎖`,
	trx_rows_lockedAS`事務鎖定行`,
	trx_rows_modifiedAS`事務更改行`
FROM
	information_schema.innodb_trx;

SELECT
	lock_id			AS	`鎖ID`,
	lock_trx_id	AS	`擁有鎖的事務ID`,
	lock_mode		AS	`鎖模式	`,
	lock_type		AS	`鎖類型`,
	lock_table		AS	`被鎖的表`,
	lock_index		AS	`被鎖的索引`,
	lock_space		AS	`被鎖的表空間號`,
	lock_page		AS	`被鎖的頁號`,
	lock_rec		AS	`被鎖的記錄號`,
	lock_data		AS	`被鎖的數據`
FROM
	information_schema.innodb_locks;

SELECT
requesting_trx_idAS`請求鎖的事務ID`,
requested_lock_idAS`請求鎖的鎖ID`,
blocking_trx_idAS`當前擁有鎖的事務ID`,
blocking_lock_idAS`當前擁有鎖的鎖ID`
FROM
innodb_lock_waits;

Ⅳ 如何 查找 mysql 中如何 kill 引起死鎖的線程id

如果遇到死鎖了，怎麼解決呢?找到原始的鎖ID，然後KILL掉一直持有的那個線程就可以了， 但是眾多線程，可怎麼找到引起死鎖的線程ID呢? MySQL 發展到現在，已經非常強大了，這個問題很好解決。 直接從數據字典連查找。
我們來演示下。
線程A，我們用來鎖定某些記錄，假設這個線程一直沒提交，或者忘掉提交了。 那麼就一直存在，但是數據裡面顯示的只是SLEEP狀態。
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo_test |
| t3 |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+----+--------+--------+------------+----+----+----+
| id | fname | lname | birthday | c1 | c2 | c3 |
+----+--------+--------+------------+----+----+----+
| 19 | lily19 | lucy19 | 2013-04-18 | 19 | 0 | 0 |
| 20 | lily20 | lucy20 | 2013-03-13 | 20 | 0 | 0 |
+----+--------+--------+------------+----+----+----+
2 rows in set (0.00 sec)

mysql> update t3 set birthday = '2022-02-23' where id = 19;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 16 |
+-----------------+
1 row in set (0.00 sec)

mysql>
線程B， 我們用來進行普通的更新，但是遇到問題了，此時不知道是哪個線程把這行記錄給鎖定了?
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)

mysql> update t3 set birthday='2018-01-03' where id = 19;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 17 |
+-----------------+
1 row in set (0.00 sec)

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 10 | root | localhost | NULL | Sleep | 1540 | | NULL |
| 11 | root | localhost | NULL | Sleep | 722 | | NULL |
| 16 | root | localhost | test | Sleep | 424 | | NULL |
| 17 | root | localhost | test | Query | 0 | init | show processlist |
| 18 | root | localhost | NULL | Sleep | 5 | | NULL |
+----+------+-----------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

mysql> show engine innodb status\G

------------
TRANSACTIONS
------------
Trx id counter 189327
Purge done for trx's n:o < 189323 undo n:o < 0 state: running but idle
History list length 343
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 11, OS thread handle 0x7f70a0c98700, query id 994 localhost root init
show engine innodb status
---TRANSACTION 189326, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 17, OS thread handle 0x7f70a0bd5700, query id 993 localhost root updating
update t3 set birthday='2018-01-03' where id = 19
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 529 page no 3 n bits 72 index `PRIMARY` of table `test`.`t3` trx id 189326 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 2; hex 3139; asc 19;;
1: len 6; hex 00000002e38c; asc ;;
2: len 7; hex 7e00000d2827c9; asc ~ (' ;;
3: len 6; hex 6c696c793139; asc lily19;;
4: len 6; hex 6c7563793139; asc lucy19;;
5: len 3; hex 8fcc57; asc W;;
6: len 4; hex 80000013; asc ;;
7: len 4; hex 80000000; asc ;;
8: len 4; hex 80000000; asc ;;

------------------
---TRANSACTION 189324, ACTIVE 641 sec
2 lock struct(s), heap size 376, 3 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 0x7f70a0b94700, query id 985 localhost root cleaning up
Trx read view will not see trx with id >= 189325, sees < 189325
上面的信息很繁多，也看不清楚到底哪裡是哪裡。
不過現在，我們只要從數據字典裡面拿出來這部分信息就OK了。
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 189324
trx_state: RUNNING
trx_started: 2013-04-18 17:48:14
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 16
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 3
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)

mysql>
原來是線程16忘掉COMMIT了。

Ⅳ 怎麼判斷mysql是否是半同步復制

在談這個特性之前，我們先來看看mysql的復制架構衍生史。 MySQL的復制分為三種： 第一種，即普通的replication。 搭建簡單，使用非常廣泛，從mysql誕生之初，就產生了這種架構，性能非常好，可謂非常成熟。 但是這種架構數據是非同步的，所以有丟失資料庫的風險。 第二種，即mysql cluster。 搭建也簡單，本身也比較穩定，是mysql裡面對數據保護最最靠譜的架構，也是唯一一個數據完全同步的架構，絕對的零丟失。不過性能就差遠些了。 第三種，即semi-sync replication，半同步，性能，功能都介於以上兩者之間。從mysql5.5開始誕生，目的是為了折中上述兩種架構的性能以及優缺點。「我們今天談論第三種架構

我們知道，普通的replication，也即mysql的非同步復制，依靠mysql二進制日誌也即binary log進行數據復制。比如兩台機器，一台主機也即master，另外一台是從機，也即slave。

1. 正常的復制為：事務一（t1）寫入binlog buffer；mper 線程通知slave有新的事務t1；binlog buffer 進行checkpoint；slave的io線程接收到t1並寫入到自己的的relay log；slave的sql線程寫入到本地資料庫。 這時，master和slave都能看到這條新的事務，即使master掛了，slave可以提升為新的master。 2. 異常的復制為：事務一（t1）寫入binlog buffer；mper 線程通知slave有新的事務t1；binlog buffer 進行checkpoint；slave因為網路不穩定，一直沒有收到t1；master 掛掉，slave提升為新的master，t1丟失。

3. 很大的問題是：主機和從機事務更新的不同步，就算是沒有網路或者其他系統的異常，當業務並發上來時，slave因為要順序執行master批量事務，導致很大的延遲。

為了彌補以上幾種場景的不足，mysql從5.5開始推出了半同步。

即在master的mper線程通知slave後，增加了一個ack，即是否成功收到t1的標志碼。也就是mper線程除了發送t1到slave，還承擔了接收slave的ack工作。如果出現異常，沒有收到ack，那麼將自動降級為普通的復制，直到異常修復。

我們可以看到半同步帶來的新問題： 1. 如果異常發生，會降級為普通的復制。 那麼從機出現數據不一致的幾率會減少，並不是完全消失。 2. 主機mper線程承擔的工作變多了，這樣顯然會降低整個資料庫的性能。 3. 在MySQL 5.5和5.6使用after_commit的模式下, 即如果slave 沒有收到事務，也就是還沒有寫入到relay log 之前，網路出現異常或者不穩定，此時剛好master掛了，系統切換到從機，兩邊的數據就會出現不一致。 在此情況下，slave會少一個事務的數據。

隨著MySQL 5.7版本的發布，半同步復制技術升級為全新的Loss-less Semi-Synchronous Replication架構，其成熟度、數據一致性與執行效率得到顯著的提升。

MySQL 5.7對數據復制效率進行了改進1 主從一致性加強支持在事務commit前等待ACK

新版本的semi sync 增加了rpl_semi_sync_master_wait_point參數 來控制半同步模式下 主庫在返回給會話事務成功之前提交事務的方式。

該參數有兩個值：

• AFTER_COMMIT（5.6默認值）

master將每個事務寫入binlog ,傳遞到slave 刷新到磁碟(relay log)，同時主庫提交事務。master等待slave 反饋收到relay log，只有收到ACK後master才將commit OK結果反饋給客戶端。

• AFTER_SYNC（5.7默認值，但5.6中無此模式）

master 將每個事務寫入binlog , 傳遞到slave 刷新到磁碟(relay log)。master等待slave 反饋接收到relay log的ack之後，再提交事務並且返回commit OK結果給客戶端。即使主庫crash，所有在主庫上已經提交的事務都能保證已經同步到slave的relay log中。

因此5.7引入了after_sync模式，帶來的主要收益是解決after_commit導致的master crash主從間數據不一致問題，因此在引入after_sync模式後，所有提交的數據已經都被復制，故障切換時數據一致性將得到提升。

• 2 性能提升支持發送binlog和接受ack的非同步化

舊版本的semi sync 受限於mp thread ，原因是mp thread 承擔了兩份不同且又十分頻繁的任務：傳送binlog 給slave ，還需要等待slave反饋信息，而且這兩個任務是串列的，mp thread 必須等待 slave 返回之後才會傳送下一個 events 事務。mp thread 已然成為整個半同步提高性能的瓶頸。在高並發業務場景下，這樣的機制會影響資料庫整體的TPS .

• 圖：Without ACK receiving thread

為了解決上述問題，在5.7版本的semi sync 框架中，獨立出一個 ack collector thread ，專門用於接收slave 的反饋信息。這樣master 上有兩個線程獨立工作，可以同時發送binlog 到slave ，和接收slave的反饋。

• 圖：With ACK receiving thread3 性能提升控制主庫接收slave 寫事務成功反饋數量

MySQL 5.7新增了rpl_semi_sync_master_wait_slave_count參數，可以用來控制主庫接受多少個slave寫事務成功反饋，給高可用架構切換提供了靈活性。

• 如圖所示，當count值為2時，master需等待兩個slave的ack

4 性能提升

• Binlog 互斥鎖改進

舊版本半同步復制在主提交binlog的寫會話和mp thread讀binlog的操作都會對binlog添加互斥鎖，導致binlog文件的讀寫是串列化的，存在並發度的問題。

• MySQL 5.7對binlog lock進行了以下兩方面優化

1.移除了mp thread對binlog的互斥鎖

2.加入了安全邊際保證binlog的讀安全

• 5 性能提升組提交

5.7引入了新的變數slave-parallel-type，其可以配置的值有：

• DATABASE （5.7之前默認值），基於庫的並行復制方式；LOGICAL_CLOCK （5.7新增值），基於組提交的並行復制方式；

MySQL 5.6版本也支持所謂的並行復制，但是其並行只是基於DATABASE的，也就是基於庫的。如果用戶的MySQL資料庫實例中存在多個DATABASE ，對於從機復制的速度的確可以有比較大的幫助，如果用戶實例僅有一個庫，那麼就無法實現並行回放，甚至性能會比原來的單線程更差。

MySQL5.7中增加了一種新的並行模式：為同時進入COMMIT階段的事務分配相同的序列號，這些擁有相同序列號的事務在備庫是可以並發執行的。

MySQL 5.7真正實現的並行復制，這其中最為主要的原因就是slave伺服器的回放與主機是一致的即master伺服器上是怎麼並行執行的slave上就怎樣進行並行回放。不再有庫的並行復制限制，對於二進制日誌格式也無特殊的要求（基於庫的並行復制也沒有要求）。

因此下面的序列中可以並發的序列為（其中前面一個數字為last_committed ，後面一個數字為sequence_number ）：

• trx1 1…..2trx2 1………….3trx3 1…………………….4trx4 2……………………….5trx5 3…………………………..6trx6 3………………………………7trx7 6………………………………..8

備庫並行規則：當分發一個事務時，其last_committed 序列號比當前正在執行的事務的最小sequence_number要小時，則允許執行。

• 因此，

a)trx1執行，last_commit<2的可並發，trx2, trx3可繼續分發執行

b)trx1執行完成後，last_commit < 3的可以執行， trx4可分發

c)trx2執行完成後，last_commit< 4的可以執行， trx5, trx6可分發

d)trx3、trx4、trx5完成後，last_commit < 7的可以執行，trx7可分發

• 綜上所述

我們認為MySQL 5.7版對Loss-Less半同步復制技術的優化，使得其成熟度和執行效率都得到了質的提高。我們建議在使用MySQL 5.7作為生產環境的部署時，可以使用半同步技術作為高可用與讀寫分離方案的數據復制方案。

Ⅵ mysql半同步復制適合哪些業務場景

說實話 這個問題 很難回答

同步復制，犧牲很多性能。slave 需要等待master處理結束 不阻塞情況。
非同步性能好，但是不等待master處理結束，不保證數據完整
半同步介於兩者之間。數據可靠性很好，但是略微犧牲性能。

業務場景。。。。 看你資料庫伺服器多屌咯。都能廣泛運用於各種場景。

如果硬要說的話，半同步復制，個人認為不適合大流量高失敗請求的網路游戲數據儲存。更適合流量一般的 非持續性的數據更替吧。比如，網商，網路訂單？

PS:絕不是安利。以前我自己搭建資料庫伺服器。後來維護麻煩，直接用了阿x雲的rds。再無煩惱。用少量金錢解決的大問題。。。。 何必自己辛苦

Ⅶ 關於MYSQL配置文件中innodb_flush_log_at_trx_commit的疑問

這是關繫到事務日誌的一個參數,0或者2,只是定時的刷新到log buffer,

事務日誌..不知道你明白不..就是你說的也影響到更新數據的操作.但對正常的數據讀寫不會有影響...簡單來說..就是你事務提交了..,你就可以查到commit後的數據. 但這時並不一定寫入到磁碟了..可能在緩存..
這個參數也就是在commit的時候會有差異,如果為1,就每個事務提交就會要刷新到磁碟後才算提交完成....這種情況是保證了事務的acid,但性能會有很大的影響...
如果為0或者2,只要commit了就算完成了...0和2的區別在於
0：當mysql掛了之後，可能會損失前一秒的事務信息
2：當mysql掛了之後，如果系統文件系統沒掛，不會有事務丟失

Ⅷ MySQL 5.7中新增sys schema有什麼好處

性能優化利器:剖析MySQL 5.7新特徵 sys schema

導讀：很多團隊在評估合適的時機切換到 MySQL 5.7，本文是在高可用架構群的分享，介紹 MySQL 5.7 新的性能分析利器。

李春，現任科技 MySQL 負責人，高級 MySQL 資料庫專家，從事 MySQL 開發和運維工作 8 年。在擔任 MySQL 資料庫 leader 期間，主要負責應用架構的優化和部署，實現了阿里巴巴 3 億 產品 從 Oracle 小型機到 64 台 MySQL 的平滑遷移。專注於研究 MySQL 復制、高可用、分布式和運維自動化相關領域。在大規模、分布式 MySQL 集群管理、調優、快速定位和解決問題方面有豐富經驗。管理超過 1400 台 MySQL 伺服器，近 3000 個實例。完成 MySQL 自動裝機系統、MySQL 標准化文檔和操作手冊、MySQL 自動規范性檢查系統、MySQL 自動信息採集系統等標准化文檔和自動化運維工具。

sys schema 由來

Performance schema 引入

Oracle 早就有了 v$ 等一系列方便診斷資料庫性能的工具，MySQL DBA 只有羨慕嫉妒恨的份，但是 5.7 引入的 sys schema 緩解了這個問題，讓我們可以通過 sys schema 一窺 MySQL 性能損耗，診斷 MySQL 的各種問題。

說到診斷 MySQL 性能問題，不得不提在 MySQL 5.5 引入的 performance_schema，最開始引入時，MySQL 的 performance_schema 性能消耗巨大，隨著版本的更新和代碼優化，5.7 的 performance_schema 對 MySQL 伺服器額外的消耗越來越少，我們可以放心的打開 performance_shema 來收集 MySQL 資料庫的性能損耗。Tarique Saleem 同學測試了一下 sys schema 對 CPU 和 IO的額外消耗，基本在 1% - 3% 之間，有興趣的同學可以參考他的這篇 blog：

（CPU Bound, Sysbench Read Only Mode）

performance_schema 不僅由於他的性能消耗大著名，還由於其復雜難用而臭名昭著。5.7 上的 performance schema 已經有 87 張表了，每個表都是各種統計信息的羅列；另外，他的這些表和 information_schema 中的部分表也纏夾不清，讓大家用得很不習慣。

sys schema VS performance schema VS information schema

現在 MySQL 在 5.7 又新增了sys schema，它和 performance_schema 和 information schema 到底是什麼關系？

Information_schema 定位基本是 MySQL 元數據信息，比如：TABLES 記錄了 MySQL 有哪些表，COLUMNS 記錄了各個表有哪些列 。

performance_schema 記錄了 MySQL 實時底層性能消耗情況，比如：events_waits_current 記錄了 MySQL 各個線程當前在等待的 event。

雖然他們之間的這個定位區別並沒有那麼明顯：比如，Information_schema 的 innodb_locks 就記錄了 innodb 當前鎖的信息，它並不是 MySQL 的元數據信息。sys schema 最開始是 MarkLeith 同學為了方便讀取和診斷 MySQL 性能引入到 MySQL 的。所以 sys schema 定位應該是最清晰的：它包含一系列對象，這些對象能夠輔助 DBA 和開發人員了解 performance schema 和 information_schema 採集的數據。

sys schema 包含了什麼？

sys schema 包含一些對象，這些對象主要用於調優和故障分析。包括：

將 performance schema 和 information schema 中的數據用更容易理解的方式來總結歸納出來的「視圖」。

提供 performance schema 和 information schema 配置或者生成分析報告類似操作的「存儲過程」

sys schema 本身不採集和存儲什麼信息，它只是為程序或者用戶提供一個更加方便的診斷系統性能和排除故障的「介面」。也就是說，查詢 performance schema 和 information schema 配置和提供格式化服務的「存儲函數」。

避免用戶在 information schema 和 performance schema 中寫各種復雜的查詢來獲得到底誰鎖了誰，每個線程消耗的內存是多少 ( 視圖 memory_by_thread_by_current_bytes )，每個 SQL 執行了多少次，大致的執行時間是多少（ 視圖 statements_with_runtimes_in_95th_percentile ）等，這些 sys schema 都直接幫你寫好，你只需要直接查詢就好了。

編寫了一些現成的存儲過程，方便你：直接使用 diagnostics() 存儲過程創建用於診斷當前伺服器狀態的報告；使用 ps_trace_thread() 存儲過程創建對應線程的圖形化（ .dot類型 ）性能數據。

編寫了一些現成的存儲函數，方便你：直接使用 ps_thread_account() 存儲函數獲得發起這個線程的用戶，使用 ps_thread_trx_info() 來獲得某線程當前事務或者歷史執行過的語句（ JSON 格式返回 ）。

當然，你也可以在 sys schema 下增加自己用於診斷 MySQL 性能的「視圖」、「存儲過程」和「存儲函數」。

sys schema 舉例

怎麼利用 sys schema 來定位問題和診斷資料庫性能？這里簡單舉一個 innodb 行鎖的例子來說明。

模擬行鎖

拿一個實際的場景來說 sys schema 能夠輔助我們分析當前資料庫上哪個 session 被鎖住了，並且提供「清理」鎖的語句。我們模擬一個表的某一行被鎖住的情況，假設表創建語句如下：

CREATE TABLE `test2` (

`id` int(11) NOT NULL,

`name` varchar(16) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

有一條數據如下：

mysql > select * from test2;

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

| id | name | age | sex |

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

| 2 | pickup1 | 1 | 1 |

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

我們分別在 session 1 和 session 2 上同時操作這條數據，這樣的話必然對同一行記錄相互有鎖死的情況，然後我們通過 session 3 來查看 sys schema 裡面的 innodb_lock_waits，確定到底是誰鎖了誰，怎麼解鎖？操作步驟如下：

通過 sys.innodb_lock_waits 查看 innodb 鎖表情況

對應的在 session 3上查看到的記錄：

mysql > select * from sys.innodb_lock_waitsG

*************************** 1. row ***************************

wait_started: 2016-05-04 01:04:38

wait_age: 00:00:02

wait_age_secs: 2

locked_table: `test`.`test2`

locked_index: PRIMARY

locked_type: RECORD

waiting_trx_id: 5382

waiting_trx_started: 2016-05-04 00:24:21

waiting_trx_age: 00:40:19

waiting_trx_rows_locked: 4

waiting_trx_rows_modified: 0

waiting_pid: 3

waiting_query: update test2 set name='pickup3' where id=2

waiting_lock_id: 5382:31:3:3

waiting_lock_mode: X

blocking_trx_id: 5381

blocking_pid: 2

blocking_query: NULL

blocking_lock_id: 5381:31:3:3

blocking_lock_mode: X

blocking_trx_started: 2016-05-04 00:23:49

blocking_trx_age: 00:40:51

blocking_trx_rows_locked: 1

blocking_trx_rows_modified: 1

sql_kill_blocking_query: KILL QUERY 2

sql_kill_blocking_connection: KILL 2

這里我們可以看到 3 號線程（ waiting_pid: 3 ）在等待 2 號線程（ blocking_pid: 2 ）的 X 鎖（ blocking_lock_mode: X ），如果需要解鎖，需要殺掉 2 號線程（ sql_kill_blocking_connection: KILL 2 ）。

innodb_lock_waits 本質

其實 sys schema 的 innodb_lock_waits 只是 information schema 的視圖而已。

CREATE ALGORITHM = TEMPTABLE DEFINER = `mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `innodb_lock_waits` AS

SELECT

`r`.`trx_wait_started` AS `wait_started`,

TIMEDIFF(NOW(),

`r`.`trx_wait_started`) AS `wait_age`,

TIMESTAMPDIFF(

SECOND,

`r`.`trx_wait_started`,

NOW()) AS `wait_age_secs`,

`rl`.`lock_table` AS `locked_table`,

`rl`.`lock_index` AS `locked_index`,

`rl`.`lock_type` AS `locked_type`,

`r`.`trx_id` AS `waiting_trx_id`,

`r`.`trx_started` AS `waiting_trx_started`,

TIMEDIFF(NOW(),

`r`.`trx_started`) AS `waiting_trx_age`,

`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,

`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,

`r`.`trx_mysql_thread_id` AS `waiting_pid`,

`sys`.`format_statement`(`r`.`trx_query`) AS `waiting_query`,

`rl`.`lock_id` AS `waiting_lock_id`,

`rl`.`lock_mode` AS `waiting_lock_mode`,

`b`.`trx_id` AS `blocking_trx_id`,

`b`.`trx_mysql_thread_id` AS `blocking_pid`,

`sys`.`format_statement`(`b`.`trx_query`) AS `blocking_query`,

`bl`.`lock_id` AS `blocking_lock_id`,

`bl`.`lock_mode` AS `blocking_lock_mode`,

`b`.`trx_started` AS `blocking_trx_started`,

TIMEDIFF(NOW(),

`b`.`trx_started`) AS `blocking_trx_age`,

`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,

`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,

CONCAT(

'KILL QUERY ',

`b`.`trx_mysql_thread_id`

) AS `sql_kill_blocking_query`,

CONCAT('KILL ',

`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection`

FROM

(

(

(

(

`information_schema`.`innodb_lock_waits` `w`

JOIN

`information_schema`.`innodb_trx` `b` ON((`b`.`trx_id` = `w`.`blocking_trx_id`))

)

JOIN

`information_schema`.`innodb_trx` `r` ON(

(`r`.`trx_id` = `w`.`requesting_trx_id`)

)

)

JOIN

`information_schema`.`innodb_locks` `bl` ON(

(

`bl`.`lock_id` = `w`.`blocking_lock_id`

)

)

)

JOIN

`information_schema`.`innodb_locks` `rl` ON(

(

`rl`.`lock_id` = `w`.`requested_lock_id`

)

)

)

ORDER BY

`r`.`trx_wait_started`

innodb_lock_waits和x$innodb_lock_waits區別

有心的同學可能會注意到，sys schema 裡面有 innodb_lock_waits 和 x$innodb_lock_waits。其實 sys schema 的這些視圖大部分都成對出現，其中一個的名字除了 x$ 前綴以外跟另外一個是一模一樣的。例如，host_summmary_by_file_io 視圖分析匯總的是根據主機匯總的文件 IO 情況，並將延遲從皮秒（ picoseconds ）轉換成更加易讀值（ 帶單位 ）顯示出來：

mysql> SELECT * FROM host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67570 | 5.38 s |

| background | 3468 | 4.18 s |

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

而 x$host_summary_by_file_io 視圖分析匯總的是同樣的數據，但是顯示的是未格式化過的皮秒（ picosecond ）延遲值

mysql> SELECT * FROM x$host_summary_by_file_io;

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

| host | ios | io_latency |

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

| localhost | 67574 | 5380678125144 |

| background | 3474 | 4758696829416 |

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

沒有 x$ 前綴的視圖是為了提供更加友好，對人更加易讀的輸出格式。帶 x$ 前綴的視圖顯示了數據原始格式，它方便其他工具基於這些數據進行自己的處理。需要了解非 x$ 和 x$ 視圖的不同點的進一步信息。

Q&A

提問：sys schema 只是在 performance_schema 和 information_schema 之上創建視圖和存儲過程？

李春：對，sys schema 主要針對的其實是 iperformance schema，有部分 information schema 的表也會整理到 sys schema 中統一展現。

提問：運行 KILL 2 殺掉 2 線程？blocking_lock_mode: X 的 X 什麼意思？

李春：blocking_lock_mode 的 X 是指 X 鎖，exclusive 鎖，排它鎖，跟它對應的是 S 鎖，共享鎖。kill 2 是殺掉 2 號線程，這樣可以將鎖釋放，讓被鎖的這個線程正常執行下去。

提問：可以放心的打開 performance_schema，為何不使用 performance_schema 再造一個 sys schema？

李春：performance schema 是 MySQL 採集資料庫性能的存儲空間。sys schema 其實只是對 performance schema 多個表 join 和整合。兩者的定位有所不同，如果直接放在 performance schema 中，分不清哪些是基表，哪些是視圖，會比較混淆。

提問：pt-query-digest 這些工具的有開始使用 sys schema 嗎？

李春：沒有，pt-query-digest 主要用於分析慢查和 tcpmp 的結果，跟 sys schema 的定位有部分重疊的地方，sys schema 會分析得更細，更內核，更偏底層一些，pt-query-digest 主要還是從慢查和 tcpmp 中抽取 SQL 來格式化展現。

提問：阿里這么多資料庫實例，使用什麼運維工具？分布式事務又是怎麼解決的呢？

李春：阿里內部有非常多的運維工具，dbfree，idb 等，用於資料庫資源池管理，資料庫脫敏，開發測試庫同步，資料庫訂正，表結構變更等。分布式事務主要通過業務上的修改去屏蔽掉，比如：電影買票並不是你選了座位和付款就必須在一個事務裡面，搶票，選座，付款分別是自己的子事務，系統耦合性比較弱，相互通知解決問題。

提問：Oracle 有 v$，MySQL 有 x$ ？兩個 $ 是完成相似功能的嗎？

李春：MySQL 的 x$ 可以說是仿照 Oracle 的 v$ 來做的，但是目前離 Oracle 的那麼強大的資料庫診斷功能還有一些距離。

提問：資料庫脫敏能否簡單介紹下實現方式？

李春：開發測試人員無法訪問線上資料庫，需要通過一個專門的 idb 來訪問，而 idb 系統每個欄位都有密級定義，滿足許可權的才能被訪問；這個系統頁控制了用戶是否可以訪問某個表，可以訪問數據表的行數，只有主管同意了，用戶才能訪問某個表的數據，並且加密數據是以*顯示的。

Ⅸ MYSQL安裝完以後如何設置

key_buffer_size - 這對MyISAM表來說非常重要。如果只是使用MyISAM表，可以把它設置為可用內存的 30-40%。合理的值取決於索引大小、數據量以及負載 -- 記住，MyISAM表會使用操作系統的緩存來緩存數據，因此需要留出部分內存給它們，很多情況下數據比索引大多了。盡管如此，需要總是檢查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 卻設置為 4GB 的情況是非常少的。這么做太浪費了。如果你很少使用MyISAM表，那麼也保留低於 16-32MB 的key_buffer_size 以適應給予磁碟的臨時表索引所需。

innodb_buffer_pool_size - 這對Innodb表來說非常重要。Innodb相比MyISAM表對緩沖更為敏感。MyISAM可以在默認的 key_buffer_size 設置下運行的可以，然而Innodb在默認的innodb_buffer_pool_size 設置下卻跟蝸牛似的。由於Innodb把數據和索引都緩存起來，無需留給操作系統太多的內存，因此如果只需要用Innodb的話則可以設置它高達 70-80% 的可用內存。一些應用於 key_buffer 的規則有 -- 如果你的數據量不大，並且不會暴增，那麼無需把innodb_buffer_pool_size 設置的太大了。

innodb_additional_pool_size - 這個選項對性能影響並不太多，至少在有差不多足夠內存可分配的操作系統上是這樣。不過如果你仍然想設置為 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的內存有多少。

innodb_log_file_size 在高寫入負載尤其是大數據集的情況下很重要。這個值越大則性能相對越高，但是要注意到可能會增加恢復時間。我經常設置為64-512MB，根據伺服器大小而異。

innodb_log_buffer_size 默認的設置在中等強度寫入負載以及較短事務的情況下，伺服器性能還可以。如果存在更新操作峰值或者負載較大，就應該考慮加大它的值了。如果它的值設置太高了，可能會浪費內存 -- 它每秒都會刷新一次，因此無需設置超過1秒所需的內存空間。通常8-16MB就足夠了。越小的系統它的值越小。

innodb_flush_logs_at_trx_commit 是否為Innodb比MyISAM慢1000倍而頭大?看來也許你忘了修改這個參數了。默認值是 1，這意味著每次提交的更新事務(或者每個事務之外的語句)都會刷新到磁碟中，而這相當耗費資源，尤其是沒有電池備用緩存時。很多應用程序，尤其是從 MyISAM轉變過來的那些，把它的值設置為 2 就可以了，也就是不把日誌刷新到磁碟上，而只刷新到操作系統的緩存上。日誌仍然會每秒刷新到磁碟中去，因此通常不會丟失每秒1-2次更新的消耗。如果設置為0就快很多了，不過也相對不安全了 -- MySQL伺服器崩潰時就會丟失一些事務。設置為2指揮丟失刷新到操作系統緩存的那部分事務。

table_cache -- 打開一個表的開銷可能很大。例如MyISAM把MYI文件頭標志該表正在使用中。你肯定不希望這種操作太頻繁，所以通常要加大緩存數量，使得足以最大限度地緩存打開的表。它需要用到操作系統的資源以及內存，對當前的硬體配置來說當然不是什麼問題了。如果你有200多個表的話，那麼設置為 1024 也許比較合適(每個線程都需要打開表)，如果連接數比較大那麼就加大它的值。我曾經見過設置為100,000的情況。

thread_cache -- 線程的創建和銷毀的開銷可能很大，因為每個線程的連接/斷開都需要。我通常至少設置為 16。如果應用程序中有大量的跳躍並發連接並且 Threads_Created 的值也比較大，那麼我就會加大它的值。它的目的是在通常的操作中無需創建新線程。

query cache -- 如果你的應用程序有大量讀，而且沒有應用程序級別的緩存，那麼這很有用。不要把它設置太大了，因為想要維護它也需要不少開銷，這會導致MySQL變慢。通常設置為 32-512Mb。設置完之後最好是跟蹤一段時間，查看是否運行良好。在一定的負載壓力下，如果緩存命中率太低了，就啟用它。

注意：就像你看到的上面這些全局表量，它們都是依據硬體配置以及不同的存儲引擎而不同，但是會話變數通常是根據不同的負載來設定的。如果你只有一些簡單的查詢，那麼就無需增加 sort_buffer_size 的值了，盡管你有 64GB 的內存。搞不好也許會降低性能。

我通常在分析系統負載後才來設置會話變數。

MySQL的發行版已經包含了各種 my.cnf 範例文件了，可以作為配置模板使用。

Ⅹ MySQL報錯Lock wait timeout exceeded怎麼辦

臨時解決辦法：

執行MySQL命令:show full processlist;

然後找出插入語句的系統id

執行mysql命令:kill id

在網路上找了想相關資料,這里摘錄如下:

首先,查看資料庫的進程信息:

show full processlist;

/*結果略,主要看id列的值*/

再查看事物表:

SELECT * FROM information_schema.INNODB_TRX\G;
/*結果略,注意結果中的trx_mysql_thread_id部分的值*/

查找對應的id,然後kill id的值即可;

mysql默認是自動提交的,可以用以下命令查看:

select @@autocommit;
值為1就是自動提交,如果你的不是1,但你又想要自動提交,那麼設置的命令是:

set global autocommit=1;

表數據量也不大，按照普通的情況來說，簡單的update應該不會造成阻塞的，mysql都是autocommit，不會出現update卡住的情況，去查看下autocommit的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

mysql>

看到亮閃閃的0，這個設置導致原來的update語句如果沒有commit的話，你再重新執行update語句，就會等待鎖定，當等待時間過長的時候，就會報ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的錯誤。
所以趕緊commit剛才執行的update語句，之後 set global autocommit=1;

2、最終解決方案：

出現該問題大多數是因為出現死鎖或者connection/session沒有關閉造成的，去檢查執行該sql的語句，檢查是否有finally{}中關閉連接。