(If you feel its too long, you can skip this first paragraph as its all reiterated later)
Barry goes back in time to stop Eobard from killing his mother because he is in an unstable emotional state and is grieving. In the s2e21 The Runaway Dinosaur, it is revealed more explicitly that Barry is still grieving over the death of his mother and how much he misses her. Barry also more recently is grieving over the very recent loss of his father Henry Allen in the episode Invincible(2.22). Zoom killed him as a way to convince Barry that they were alike, and his father essentially died as a means of making a point. It is here that Barry's emotional state falters, and his plan changes from stopping zoom to killing zoom. (Turning Barry into a killer is exactly what zoom wanted) His closest friends see this and therefore conspire behind his back to defeat zoom without him. This betrayal sticks in Barry's mind, and he is an outsider among all but Wally, and feels killing zoom is the only way to stop him from continuing to terrorize other earths. The final straw though isn't just his father's death, but the revelation that the real Jay Gerrik which zoom had kept locked in a cell for months (?) Is his dads doppelganger. Barry is so heartbroken, especially since it's only been days (?) since the loss of his father, that he has to leave the room to cry in the hallway. Everything is too much for him, and this revelations shows how even after zoom is defeated, somehow the universe is still finding ways to make him suffer. When he defeats zoom by creating a time remnant of himself, it further reinforces how Barry will do anything, including sacrifice himself, for those he loves. This is also seen when Barry gives up his powers to zoom in order to save Wally (the son Joe should have had if it weren't for Barry becoming an orphan). Barry gave up his powers for him who he knew least, and did it because of how much Joe cared for him. Barry is willing to give up his powers and his life for those he loves.
At the end of the episode Barry decides to go back in time to stop Eobard from killing his mother because in his mind, being the flash has caused more harm than good to those closest to him. Being the flash led to the death of his mother as well as the wrongful imprisonment of his father for years, and then his death. Barry feels immensely responsible and guilty for the death of earth 2's Joe West (2.13. Welcome to Earth 2). Eddies death in the season 1 finale we know he feels guilty about when he returns from Earth 2 with a special video message from Eddie to iris). Even the particle accelerator explosion was intentionally made to create the flash and led to the creation of meta human villains. Barry being the flash has caused all this damage, and his father's death (and Jay Gerrik being his father's doppelganger) is the final straw.
This episode ends with zoom being defeated, and yes everything appears, as we watch Barry gaze longingly into the house, okay. He even gets to be with Iris, sharing their first genuine kiss, which is everything he's always wanted. But as he looks back into the house, taking everything written above and seen in the show into consideration, Barry is not happy.
His closest friends get to come home to their families at the end of the day, but Barry, despite having a new growing family with everyone, is an outsider who doesn't have any real family to call his own. He is alone and sad and thinks that the only way to stop himself from the extreme loss he's felt as a result of being the flash is to go back in time and save his mother. This act is a selfish one. Barry knows that saving his mother will mean he will no longer be the flash, but as we know from previous experiences, Barry is willing to give up his powers for someone he barely knows, so giving them up for his mother's life isn't hard at all for him. Barry gives up a life with Iris and Joe everything else to change the past because all the pain and sadness and anger he's built up over the last 2 years of being the flash is too much for him, and saving his parents is the only thing that can give him true happiness and fulfillment.
As for what he expects to happen, I think Barry thinks that by not becoming the flash, he will be able to live a peaceful normal life with both his parents, and that perhaps another person will take up the title of the flash, or no one will. But, as you stated, Barry's time traveling adventures haven't all been successful, so changing this significant moment might not lead to the ideal life that he wishes he could have. But he decides to do it anyway, because the idea of having both his parents with him is more desirable than the life he's made for himself as the flash.
As for the show, it is highly likely they will be following the flashpoint paradox storyline, in which similarly, (spoiler) Barry after still grieving his mother's loss, goes back in time to save her, and therefore creates an alternative dystopian reality.
