CODEWITHSUNDEEP
phpmysqldatabasetransactionsacid
untitled
untitled

Reputation: 405

MySQL Transactions vs Locks

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!

Upvotes: 3

Views: 525

Answers (1)

Alex.Ritna
Alex.Ritna

Reputation: 1984

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.

Upvotes: 1

Related Questions