MySQL：SELECT GET_LOCK 慢/超時
我正在使用“ Zebra Session ”作為 PHP 內置會話管理的替代品。Zebra Session 將會話儲存在 MySQL 數據庫中，並使用“會話鎖定”來確保在具有多個並發 AJAX 請求的場景中正確處理數據。
會話鎖定是通過使用
SELECT GET_LOCKZebra_Sessionread函式中的 sql 命令實現的。通常這工作正常，但在過去的幾天裡，這些 sql 語句一直在填滿我的慢日誌。其中一些聲明在非常不合時宜的時刻超時。這是我的慢日誌中的範例語句：# Query_time: 60.000150 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SELECT GET_LOCK("session_p494bdabuh56tddmbv19g8i9d7", 60);我意識到，如果會話先前已被鎖定，則
SELECT GET LOCK命令將掛起，直到鎖定被釋放或命令超時，但我想不出這種情況實際發生的情況。我正在使用 InnoDB - 這是以下輸出
SHOW ENGINE INNODB STATUS：===================================== 150224 11:55:16 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 38 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 72097 1_second, 72077 sleeps, 7094 10_second, 1251 background, 1250 flush srv_master_thread log flush and writes: 74597 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 18608, signal count 79708 Mutex spin waits 238350, rounds 331918, OS waits 6599 RW-shared spins 33512, rounds 348663, OS waits 10791 RW-excl spins 15495, rounds 99335, OS waits 1173 Spin rounds per wait: 1.39 mutex, 10.40 RW-shared, 6.41 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 19BC0896 Purge done for trx's n:o < 19BC0836 undo n:o < 0 History list length 999 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 144709, OS thread handle 0x7fdac5fc5700, query id 2569619 localhost root show engine innodb status ---TRANSACTION 0, not started MySQL thread id 140098, OS thread handle 0x7fdac6c36700, query id 2510580 38.105.174.189 root -------- 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 101050 OS file reads, 767519 OS file writes, 210806 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 9.37 writes/s, 3.21 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 82794, seg size 82796, 1782 merges merged operations: insert 1718, delete mark 123, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249871, node heap has 1239 buffer(s) 11479.22 hash searches/s, 1909.58 non-hash searches/s --- LOG --- Log sequence number 53832600944 Log flushed up to 53832600944 Last checkpoint at 53832546459 0 pending log writes, 0 pending chkp writes 159378 log i/o's done, 2.74 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 1152378 Buffer pool size 655360 Free buffers 542707 Database pages 111414 Old database pages 41146 Modified db pages 58 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 65, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 100882, created 10532, written 591116 0.00 reads/s, 0.00 creates/s, 6.45 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: 111414, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 22747, id 140577666729728, state: sleeping Number of rows inserted 415334, updated 148965, deleted 29844, read 1412355001 0.55 inserts/s, 2.05 updates/s, 0.03 deletes/s, 31796.27 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT這是 MySQL 配置文件 (my.cnf)：
[mysql] # CLIENT # port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = root default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # CACHES AND LIMITS # tmp-table-size = 32M max-heap-table-size = 32M query-cache-type = 1 query-cache-size = 2M query-cache-limit = 2M max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 512 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 10G # LOGGING # log-error = /var/log/mysql-error.log log-queries-not-using-indexes = 0 long_query_time = 3 slow-query-log = 1 slow-query-log-file = /var/log/mysql-slow.log我會很感激一些幫助，以了解這一點。
對不起那個遲到的答案和我糟糕的英語:)
目前我正在使用 ZebraSession 和我自己的 ClientCookie-Session 類進行會話混合。我很害怕聽說 GET_LOCK，所以我開始搜尋它的用法。現在我偶然發現了你的文章。
可能是您（或所有 ZebraSession 使用者）的問題
a) 取決於您正在執行的 MySql 版本。b）您正在使用持久連接（在 php-manual 中搜尋持久 mysql conns）
如果您訪問以下頁面：
http://techblog.procurios.nl/k/news/view/41405/14863/mysql-get_lock%28%29-explained.html
並向下滾動到最後一條評論（由 GDmac - 發佈於：09-10-2012 21:14），您可以閱讀它。
我希望它有所幫助；）
我點擊了這個問題，因為我不知道
GET_LOCK()MySQL 中有一個函式。據我所知，如果該鎖尚不存在，它只會創建一個命名鎖。如果存在，則該語句使 MySQL 客戶端會話等待直到鎖被釋放。
因此，如果您的所有呼叫在此函式呼叫中使用相同的名稱（即：所有日誌都是
SELECT GET_LOCK("session_p494bdabuh56tddmbv19g8i9d7", 60)），那麼這意味著您有很多並發請求，最新的請求必須等待所有這些請求完成才能繼續。如果每個會話的名稱是唯一的，那麼同樣適用，但僅涉及每個使用者的會話。
所以如果我是你，我會檢查 MySQL 的完整查詢日誌：從你的慢查詢日誌中選擇一個死鎖（超時
GET_LOCK），然後查看通用查詢日誌以獲取GET_LOCK相同會話名稱的每個呼叫，然後查看查詢這些 MySQL 會話執行：它們可能很長（因為現在，您的數據庫中的數據比開始時更多）並且需要超過 60 秒。如果所有查詢都不需要大約 60 秒，然後檢查您的客戶端程式碼是否正確釋放鎖並正確釋放 MySQL 會話（即：Java 可能會在發生異常/SIGNAL時將資源打開給 MySQL，從而使 MySQL 會話保持打開狀態，所以不釋放鎖，因此鎖定所有其他GET_LOCK呼叫）。