3

On dev mysql LOCK IN SHARE MODE is described as...

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.

Does that mean that if I simply call

$dbc -> beginTransaction();

And either commit or rollback then the data read will be the latest given? Because it also says that using FOR UPDATE will block LOCK IN SHARE MODE from reading (ie make sure it is the latest info).

But if I wrap all my important updates and deletes in a transaction then is there still a need to lock rows, as above it says LOCK IN SHARE MODE will wait for a transaction to finish? Surely if it waits for transactions to finish then essentially I will always have the freshest data available by just using transactions and not having to lock when performing updates and deletes?

Improve this question
1
  • What's the exact problem you're trying to solve here? Transactions are (usually) isolated from each other. If there's a specific case when you want to see the data from another (uncommitted) transaction - you can use SELECT LOCK IN SHARE MODE. If it's a case of you starting a transaction in piece of PHP code and then using commit or rollback - actions are performed on that transaction, not some random "latest" one. Commented Jan 2, 2012 at 10:54

1 Answer 1

Reset to default
1

Performing SELECT...LOCK IN SHARE MODE can block UPDATE...LOCK IN SHARED MODE. Deadlocks can still occur. Needed rows for SELECT or UPDATE can still introduce locks you never intended to issue. What do I mean ?

It is sometimes necessary to internally lock primary key entries for your own good.

I once made three posts helping a developer realize this : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

Improve this answer