Xử lý mysql locking

[Duy_Hoang98 (DS)]

em có 1 table ntn

em muốn khi select data từ table này khi sẽ không bị trùng dòng ( vì em cron chạy liên tục lấy dữ liệu, dễ bị lấy trùng nhau ), code test em lấy ntn

$get_data = mysqli_query($conn, "SELECT id, name, age FROM locking LIMIT 2 FOR UPDATE SKIP LOCKED");

while($data = mysqli_fetch_assoc($get_data)){

    echo $data['id'].'-'.$data['name'].'<br />';

    sleep(2);

}

mysqli_query($conn, 'COMMIT');

• nếu viết code kia vào 2 file khác nhau, thì khi chạy song song 2 file, file đầu tiên nó sẽ lấy 2 dòng đầu và locking 2 dòng đó lại, file thứ 2 nó sẽ lấy 2 dòng tiếp theo vì 2 dòng đầu bị lock lại rồi ( đúng yêu cầu).

• nhưng khi cho code vào 1 file, và em thử chạy mỗi tab trình duyệt 1 file (e enter chạy song song cách nhau 0.5s) thì nó lại chỉ lấy được 2 dòng đầu. (tab 1 load xong rồi tab 2 mới xong )

mn giiusp e vs ak. e cảm ơn

[Duy_Hoang98 (DS)]

à, em test thử trên 2 tab của 2 chrome riêng biệt thì được rồi ạ.

còn vấn đề là trong khi select để xử lý thì nếu mà mình insert vào table đó nó sẽ bị lỗi
Deadlock found when trying to get lock; try restarting transaction

làm sao để tránh lỗi này nhỉ, trong khi insert mới dữ liệu ko ảnh hưởng gì đến việc select data rồi xử lý

[library (La biblioteca)]

Cậu show tớ kết quả câu lệnh này trong khi chạy select for update được không?

SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;

Vì cậu không select sử dụng unique index, innoDB khả năng cao sẽ sử dụng gap lock để lock tất cả các record có trong bảng (phỏng đoán của tớ thôi, cậu cần chạy câu lệnh trên để chứng minh điều đó). Đó là lý do cậu gặp deadlock.

Sau khi xem kết quả câu lệnh trên, tớ nghĩ có thể lúc đó mới có giải pháp cho cậu. Nếu cậu nôn nóng và muốn giải pháp mà không cần quan tâm tới nguyên nhân, cậu có thể work around bằng cách set transaction isolation level xuống READ COMMITED. Deadlock sẽ biến mất khi đó.

[Duy_Hoang98 (DS)]

@library

SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;

em chạy query a gửi nhưng lỗi không thấy table data_locks, e đang dùng mariadb 10.6

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

web e thường phải select ko sử dụng where với các column unique/primary, vậy thì gap lock nó sẽ lock all table ạ, e tưởng nó sẽ chỉ lock các rows khớp với query. và nếu lock rows thì sao khi insert lại xảy ra deadlock nhỉ a, e nghĩ phải lock table thì mới ko thể thực hiện select,insert,update,delete trong table đó ( e chưa hiểu lock rows và lock table nó ntn, xảy ra khi nào trong mysql)

với cả hiện tại khi insert vào bảng đó từ nhiều session cùng lúc thì thi thoảng bị vài lần lỗi Deadlock ( ko phải thường xuyên)

và nếu set transaction isolation level xuống READ COMMITED, cách này có nhược điểm gì không ạ

[Dark.Hades (Ichiro)]

Hình như phải begin transaction thì nó mới lock thực sự

[library (La biblioteca)]

web e thường phải select ko sử dụng where với các column unique/primary, vậy thì gap lock nó sẽ lock all table ạ

Không đâu cậu. Tùy vào câu query mà nó sẽ lock các row có thể được đề cử.
Tớ presume cậu đang sử dụng default isolation level của InnoDB (repeatable read). Ở TH của cậu, cậu không có where clause, và dùng limit để giới hạn 2 bản ghi. Tớ không nghĩ InnoDB sẽ lock 2 bản ghi, mà nó sẽ căn cứ vào where clause của cậu (lấy tất cả bảng) và lock tất cả các record mà câu query có thể trả về (limit là thao tác xảy ra cuối cùng, sau khi câu query chạy xong), hoặc nó sẽ lock các row lân cận của record. Đó là phỏng đoán, và chỉ có thể được chứng minh khi cậu chạy câu lệnh để xem các row bị lock
Cậu có thể đọc thêm về behavior của gap locking ở đây

và nếu lock rows thì sao khi insert lại xảy ra deadlock nhỉ a

Nếu gap locking xảy ra (và tớ nghĩ nó đã xảy ra), thì cậu hoàn toàn có thể gặp deadlock

với cả hiện tại khi insert vào bảng đó từ nhiều session cùng lúc thì thi thoảng bị vài lần lỗi Deadlock ( ko phải thường xuyên)

Ừ cậu, lỗi concurency thường chỉ xảy ra một cách ngẫu nhiên và không đoán được.

và nếu set transaction isolation level xuống READ COMMITED, cách này có nhược điểm gì không ạ

Đây là 1 cách để disable gap lock mà tớ kể ở trên.
Tất nhiên, khi cậu set transaction isolation level xuống READ COMMITED, cậu sẽ gặp vấn đề với non repeatable read. Cậu cần cân nhắc để xem nó có ảnh hưởng tới các tính năng khác hay không. Cơ mà, READ COMMITED là default isolation level ở PostgreSQL, nên tớ không nghĩ có nhiều vấn đề đâu.

em chạy query a gửi nhưng lỗi không thấy table data_locks, e đang dùng mariadb 10.6

Uhm, tớ tưởng cậu dùng mysql cơ
Cậu thử tìm câu lệnh nào để xem được row lock ở mariaDb xem Tớ chưa search thử, nhưng nên có cách tương tự.

[Duy_Hoang98 (DS)]

mình có START TRANSACTION rồi mà copy code bị thiếu thui

[Duy_Hoang98 (DS)]

thank a để e tìm hiểu thêm

[Duy_Hoang98 (DS)]

Lỗi Deadlock em đã hạn chế được tối đa rồi ak, nhưng giờ nó lại bị lỗi Lock wait timeout exceeded; try restarting transaction (khi thực hiện INSERT trong khi đang select các row và lock để xử lý xong mới commit)

E có thử set transaction isolation level READ COMMITED trước dòng insert nhưng nó vẫn bị vậy. Khi test e thấy ở file select để xử lý khi nào commit thì INSERT mới được chạy, nếu file đó xử lý quá lâu sẽ gây timeout và xảy ra lỗi trên.

[library (La biblioteca)]

Uhm, tớ nghĩ có khi cậu nên đổi chiến lược lại. Có lẽ sẽ tốt hơn nếu cậu để isolation level về REPEATABLE READ, và cân nhắc cậu sẽ làm gì khi deadlock xảy ra.
Khi deadlock được detect (vốn xảy ra tương đối nhanh, so với việc chờ lock cho tới khi timeout), innoDB sẽ restart 1 trong 2 transaction gây ra deadlock. 1 trong 2 transaction sẽ thành công, transaction còn lại có thể thất bại. Sẽ tốt hơn nếu cậu cân nhắc cài đặt logic để retry lại transaction khi bị thất bại, hơn là chữa “triệu chứng”. Deadlock trong TH của cậu không phải vấn đề xấu đâu, đó là tính năng mà innoDB detect và giúp cậu không phải chờ lock cho tới khi timeout.
Cậu sẽ gặp vấn đề với dead lock với cách dùng hiện tại, điều đó không tránh khỏi

Hope it helps!

[nguyenhuuca (Nguyen Ca)]

select table(with nolock)

tương đương:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Còn tránh deadlock tuyệt đối, thì phải xử lý tuần tự, và ko có phụ thuộc a->b, b->a