CODEWITHSUNDEEP
phpmysqltransactions
Vpp Man
Vpp Man

Reputation: 2546

confused on mysql transactions in php

if I use transactions, will it lock the tables and prevent it from making any changes by other users?

pseudo code:

 begin transaction
   issue a select query
   issue a update query
 end transaction

so, in between those two queries, is it possible to make some changes by another update statement or something which was issued from another page ?

or since the beginning of the transaction, will the tables used be locked ?

What is the difference between transaction and lock table? will a transaction implicitly lock the table ?

Edit: This is what i want to do:

{
    // Check Table2 (posted messages)
    // If is_approved is FALSE for a given msg_id
    {
        // Set is_approved to TRUE
        // Update Table1 (member details) post_count_month++
        //     and post_count_lifetime++
    }
    // Else
    {
        // NOOP
    }
}

Above updation can be made by several users at the same time. Also, a single user(Admin) could delete a message(both accepted and not accepted message). So for deleting, the countmonth and countlifetime of the user who had posted that message(the one to be deleted) should be incremented and then message is deleted.

This is the two situation I am facing.

Upvotes: 3

Views: 308

Answers (3)

Álvaro González
Álvaro González

Reputation: 146563

Transactions and table locking are different features that solve different problems. In your case you only seem to perform one write operation into the database, thus using a transaction will not provide any benefit unless you want to able to undo the update.

Have a look at the SELECT ... FOR UPDATE statement.

Update #1:

Alright, here you are some code:

START TRANSACTION;

SELECT member_id, is_approved
FROM posted_messages
WHERE msg_id=31416
FOR UPDATE;

UPDATE member_details
SET post_count_month=post_count_month+1, post_count_lifetime=post_count_lifetime+1
WHERE member_id=123;

UPDATE posted_messages
SET is_approved=TRUE
WHERE msg_id=31416;

COMMIT;

(I'm not sure about how helpful it'll be, considering that I've invented all the queries. Understanding the code is always better than copying and pasting.)

Update #2:

I've edited the sample code to surround the queries in a transaction. I said that transactions and table locking are different features and it's still true. However, the SELECT ... FOR UPDATE works inside a transaction. It's just an implementation detail.

Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

Upvotes: 2

Adrian Cornish
Adrian Cornish

Reputation: 23896

First of all it depends on the storage engine you use for your tables. All sorts of weird things can happen with various mysql settings/storage engine options.

If for example you have InnoDB tables and allow dirty reads - It means other client can read Ghost Rows - which dont exist - are different etc

Upvotes: 0

iWantSimpleLife
iWantSimpleLife

Reputation: 1954

Transactions are designed to make database operations atomic (or non-divisible).

Imagine the problem you will have if you are doing an increment.

First you read the value as 1, then you add 1 to it to become 2, then you write 2 back to the database.

If at the point inbetween your adding 1 and writing back to the database, someone else does the same and read the value of 1, the maybe subtract 1, then write back as 0. One of the values will be lost. The end result will either be a 2, or a 0 cepending on the order the updates are applied.

(see http://en.wikipedia.org/wiki/Race_condition for more info)

As for the locking of tables during transactions, It will depends on the database management system used.

For some, it will not lock the tables, but will raise an exception when the updates will cause discrepencies with the result. Forcing what is called a "automatic roll back".

For other dbms, they might implement it using locks.

Upvotes: 1

Related Questions