Jump to content

Transactions Help


Recommended Posts

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 by sckewi
Explained in post
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...