共享锁,排他锁的现实理解

Think of a lockable object as a blackboard (lockable) in a class room containing a teacher (writer) and many students (readers).
While a teacher is writing something (exclusive lock) on the board:
Nobody can read it, because it's still being written, and she's blocking your view => If an object is exclusively locked, shared locks cannot be obtained.
Other teachers won't come up and start writing either, or the board becomes unreadable, and confuses students => If an object is exclusively locked, other exclusive locks cannot be obtained.
When the students are reading (shared locks) what is on the board:
They all can read what is on it, together => Multiple shared locks can co-exist.
The teacher waits for them to finish reading before she clears the board to write more => If one or more shared locks already exist, exclusive locks cannot be obtained.
【共享锁,排他锁的现实理解】LOCK IN SHARE MODE 适合用于两张表存在业务关系上的一致性要求时的操作场景。
SELECT ... LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作,拿MySQL官方文档的例子来说,假如存在两张有关系的表:PARENT和CHILD,使用普通的SELECT语句(快照读)来查询表PARENT并验证父行是否存在后再将子行插入CHILD表,这种方式安全吗?答案是否定的,因为其他会话可能会在你这个会话的SELECT和INSERT之间的某个时间点删除了父行,这个删除操作你是无法察觉到的。
为避免这种潜在的问题,我们使用“加共享锁”的方式执行SELECT

    推荐阅读