MySQL Transactions vs Locks

Question

I'm building an application in which users can submit a post, and people can reply to the post. When a user posts a post, he can select which users can view the post.

This is the posts table:

| id | poster-id | title | message | ... |

This is the posts-visible table. It represents individual relations between posts and members (a relation that means they can view and reply to the post):

| id | post-id | user-id |

This is the replies table:

| id | posts-id | poster-id | title | message | ... |

Right now, when someone adds a reply, I run the following in a transaction:

try
{
    $db->beginTransaction();
    // first check if the user is part of the posts-visible group
    $stmt = $db->prepare('SELECT id FROM `posts-visible` WHERE posts-id=:pid AND poster-id=:uid LOCK IN SHARE MODE');
    // ... bind and execute

    if($stmt->rowCount() <= 0) return false; // not in visible group

    $stmt = $db->prepare('INSERT INTO `replies` ... ');
    // binds and execute

    $db->commit();
}
catch(Exception $e)
{
    $db->rollback();
}

My question is: is the LOCK IN SHARE MODE necessary? I envisioned a possible race condition in which the original poster removes access to the post for a user just as the user posts a reply, in which he would be able to post a reply despite the poster disallowing him access, so I put LOCK IN SHARE MODE so that other sessions would be able to read but not modify it. However, does the transaction not already guarantee synchronized access through Isolation in ACID that that won't happen? If so, when would LOCK IN SHARE MODE be appropriate? Why use it with a transaction?

As a second question, I also have some code that fetches information about posts (e.g. post content, title, etc.). The code is similar, except instead of an INSERT I have another SELECT after the first SELECT that checks for access permissions. Are transactions even necessary, since nothing is being written? Does that change anything from the first answer?

And finally, another question. If I have a transaction that checks if a row exists before inserting it, will transactions prevent another transaction from inserting a row in between the row check and the insert?

Thanks!

I do have foreign key constraints between the relevant columns in the tables. However, there isn't a relation between posts-visible and replies, so I don't see how that would work. For example, if the original poster removes the entry row in posts-visible for a user, there's no constraint against the replies table. — untitled, May 19 '14 at 01:41
From what I've read though, isn't locking/transactions more suitable for this? Logically, I don't see a clear connection between the permission and the post. Also, I want posts that the user created before to remain; he just can't post anymore after his permission is revoked. — untitled, May 19 '14 at 01:45
"isn't locking/transactions more suitable for this" --- it depends. I would avoid locks as much as possible. Your solution prevents 2 transactions to insert rows simultaneously. — zerkms, May 19 '14 at 01:46
Doesn't `LOCK IN SHARE MODE` allow multiple sessions to share locks and only prevent writing, while allowing updating? Therefore, if two transactions are trying to write, wouldn't they still be able to insert, since the actual INSERT operation doesn't affect the locked row? — untitled, May 19 '14 at 01:49
The LOCK IN SHARE MODE is intended to block other sessions from revoking the posts-view permission while the transaction is in progress (INSERTing the reply in the replies table). My question is whether the lock is needed because of the transaction. — untitled, May 19 '14 at 02:01
A lock is always created during a insert, update or select. It is how that lock is honoured by other operations that is more important to understand — Namphibian, May 19 '14 at 03:08
`LOCK IN SHARE MODE` explicitly allows reading though. It only blocks write operations. — untitled, May 19 '14 at 04:24

score 1 · Answer 1 · edited May 23 '17 at 12:11

My question is whether the lock is needed because of the transaction.

The short answer is yes, you do.

All the transaction will do is give you means to rollback if something down the track failed. Without the lock there is no way of knowing if in the time between your two queries the underlying table is modified.

Keep in mind, presuming you're using InnoDB, your SELECT ... LOCK IN SHARE MODE will only lock the selected row(s) not the entire table as documented here. I highly recommend reading through this documentation in particular the difference between the two methods and when to use each.

This same concept also applies to your second question, and is explained quite well here in this answer.

In the end, it boils down to this:

Locks keep anyone else from interfering with any database records you're dealing with. Transactions keep any "later" errors from interfering with "earlier" things you've done. Neither alone can guarantee that things work out ok in the end. But together, they do.

in tomorrow's lesson: The Joy of Deadlocks.

Thanks for the response! If the transaction on exists to allow an All-or-Nothing set of queries, how does it fulfill the Isolation in ACID? And if that is so, does that mean that there is no point in using transactions for only SELECTs, since the LOCK is doing the job of making sure no data changes, and the transaction is only useful if things are being modified? Also, the linked page is broken :( — untitled, May 19 '14 at 04:25
@untitled I have updated the link, for some reason SO added a www. at the start on its own. Yes in your context the transaction probably isn't needed. It's more for when you want to roll back the changes you've made if something goes wrong, such as making a handful of changes and either they all succeed, or none do (you ROLLBACK). — Alex.Ritna, May 19 '14 at 06:53

MySQL Transactions vs Locks

1 Answers1