为何SKIP LOCKED在基于语句的复制中是不安全的？
Great question! Let's break down why SKIP LOCKED is flagged as unsafe for statement-based replication (SBR) and what that "unsafe" label really means.
Why SKIP LOCKED Breaks Statement-Based Replication
First, let's recap how SBR works: it replicates the exact SQL statements executed on the master server to the slave. The slave runs those same statements to stay in sync with the master. For this to work flawlessly, every statement must produce identical results on both servers—no exceptions.
Here's where SKIP LOCKED causes problems: this clause makes a query skip rows that are currently locked by other running transactions. The rows that get skipped aren't determined by the statement itself—they depend entirely on the dynamic lock state of the server at the moment the query runs.
On the master, when you execute a SKIP LOCKED query, it skips rows that are locked right then. But by the time that same statement reaches the slave, the lock state on the slave is completely independent (the slave's locks come from its own local transactions, not the master's). This means the slave will almost certainly skip different rows than the master did. Over time, this leads to the master and slave having divergent data—their datasets won't match anymore.
What "Unsafe" Actually Means
The "unsafe" label here directly refers to replication inconsistency. When MySQL marks a statement as unsafe for SBR, it's telling you there's no guarantee the statement will produce the same result on the slave as it did on the master. This leads to several critical issues:
- Data drift: The slave's data slowly becomes out of sync with the master, making it unreliable for reads or failover.
- Application errors: If your app reads from the slave, it might get incorrect or missing data that doesn't exist on the master.
- Failed failover: If you need to promote the slave to master, its mismatched data will cause problems for your entire system.
A Quick Example to Illustrate
Let's say you have an orders table with rows 1, 2, and 3:
- On the master, Transaction A locks row 2.
- Transaction B runs
SELECT * FROM orders FOR UPDATE SKIP LOCKED—it returns rows 1 and 3. - This statement is sent to the slave for replication.
On the slave, there's no transaction locking row 2 (since the slave's transactions are separate from the master's). When the slave runs the same SELECT ... SKIP LOCKED statement, it returns all three rows (1, 2, 3). Now the master and slave have different results from the exact same query—replication consistency is broken.
The Fix
If you need to use SKIP LOCKED, switch to row-based replication (RBR). RBR replicates the actual row changes (not the SQL statement) from the master to the slave. Since it doesn't rely on executing the same query on the slave, it doesn't care about the slave's lock state—so SKIP LOCKED works safely with RBR.
内容的提问来源于stack exchange，提问作者gal.m
