Transactions Help

[sckewi]

Hello there and Happy New Year!

I'm a little confused about transactions in MySQL.

Let's say we have a "comment_thread" table and a "comments" table. The comments table has a "comment_thread_id_num" column so we can select all comments from that thread using it.

The "comment_thread" table has a "num_comments" table which keeps count of how many comments are in this particular thread.

Now, let's say we have an AddComment function, something like this.

function AddComment( $ThreadIDNum, $CommentText )
{
	// Step 1 - Insert comment into comments table

	// Step 2 - Update comment_thread, add one to num_comments
}

Now, we want to use a transaction. Because if we lose database connnection between step one and step 2, the num_comments column will be incorrect. So let's do that

function AddComment( $ThreadIDNum, $CommentText )
{
	// Step 1 - Begin transaction

	// Step 2 - Insert comment into comments table

	// Step 3 - Update comment_thread, add one to num_comments

	// Step 4 - Commit transaction

	// Step 5 - Roll back if failed?
}

So, here's where I'm getting confused. With my current web host, occasionally I am experiencing losing my database connection half way through a script. So I figure if I use a transaction like above, ether both steps will be completed, or they wont. There shouldn't be any concept of losing connection between the two steps, because we send BOTH queries to the database at the same time in step 4

So then, what is the point in rolling back? Assuming there's no errors in my syntax, the queries either rached the the database and worked or they did not? In fact, if we've lost connection roll back won't work anyway?

I'm pretty sure I'm misunderstanding something here.

Any help would be much appreciated!

Edit: Also, appologies I probably should have put this in the SQL section of the forum.

Edited January 1, 2020 by sckewi
Explained in post