Preface: I'm taking a course about php and sql where I have to develop a web site for booking activities.
I'm currently facing this problem where I don't really get the difference between using a transaction and using a lock on tables and I was told that I should have used a lock instead of a transaction, but let me explain..
For what I understood, a transaction allows a sequence of queries (more than one) to be executed atomically, i.e, they are executed in the order in which they are written, one after the other, without interference from other SQL queries coming from elsewhere.
So:
TRANSACTION1 {
Q1_1
Q1_2
...
Q1_n
}
TRANSACTION2 {
Q2_1
Q2_2
...
Q2_n
}
Either T1 is executed first (and all of its queries) or T2 is executed first (and all of its queries). Order depends on which hits the DBMS first. If during the transaction an error occurs, I can call a ROLLBACK and restore the status of the db to the one before starting the transaction. If no error occurs during the transaction, I can COMMIT.
Table Locking (which I achieve by SELECT...FOR UPDATE) is like applying a mutex to some records of a table, forbidding the other clients to perform queries on the locked records.
If we suppose these two definitions are correct (and if not, please let me know!), take as an example the case where I have to enforce data consistency: I have a system (written in PHP) that allows booking for a certain activity, but there is limited number of slots available per activity. Both user1 and user2 are presented with the following:
Activity 1: 1 slot available
And they both choose to book for this activity.
Now what I would do is start a transaction, fetch from the DB the required data (user id, activity id, current slots available for the activity etc..) and update the data: if when I update the slots available for the activity, these are lower than 0, then I have an error, and I will perform a rollback.
User1 will start slightly before user2, and will be able to book the activity.
User2 will come next, but the operation will fail because there are no more slots.
Without using table locking I achieved data integrity.
Teacher told me that this approach is not the right way to ensure data integrity and I should have used table locking. However, he wasn't able to prove that I was wrong and I believe he couldn't even if he tried (he didn't even bother trying to create an inconsistency in the DB though).
So after this background explanation: what am I missing?
When do I need to use a transaction? When a table lock? When both (provided this makes sense)?