Jump to content
sckewi

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...