php PDO Transactions

[kartul]

Hello all.

I have never used transactions before and I'm building a website right now and trying to use one. Now, the question here is do I use it right?

I have 3 tables, posts, users and users_to_posts which holds all ids (id, user_id, post_id). Now as I need to get the last insert id from database, I need to lock tables cause when two people post at the same time, it might get all mixed up. Now I use the lock tables statement first time. Anyway, all this story aside, the question. Do I use this block right?

if(isset($_SESSION['uID'], $_SESSION['username']))
        { // User
            try
            {
                $dbh->beginTransaction();
                $stmt = $dbh->exec('LOCK TABLES `posts` WRITE, `users_to_posts` WRITE');
                
                $insert = $dbh->prepare('INSERT INTO `posts` (`content`) VALUES (:content)');
                $insert->bindParam(':content', $post, PDO::PARAM_STR, 350);
                $insert->execute();
                
                $jointbl = $dbh->prepare('INSERT INTO `users_to_posts` (`user_id`, `post_id`) VALUES (:uID, :pID)');
                $jointbl->bindParam(':uID', $_SESSION['uID'], PDO::PARAM_INT, 11);
                $jointbl->bindParam(':pID', $dbh->lastInsertId(), PDO::PARAM_INT, 11);
                $jointbl->execute();
                
                $unlock = $dbh->exec('UNLOCK TABLES');
                $dbh->commit();
            }
            catch(PDOException $e)
            {
                echo $e->getMessage();
            }
        }

 

Oh, just in case. There are no errors, it works. I just want to know if it's the right way to use it.

[JasonLewis]

My understanding of it is that you only really use them when you may need to roll back changes you make within MySQL if something goes wrong. From my point of view it just doesn't look like you really need to use transactions here, but then again, I'm still delving into PDO myself.

 

That said, you could simply use rollBack() in your catch if something does go wrong.