Should you revise a manuscript when only two of the three reviewers provide a positive review? The title is a bit vague by design. What I was actually wondering is, what happens if 1 out of 3 reviewers decides that the paper should not be accepted, but the other 2 reviewers find it interesting and suggest for it to be accepted? Must all 3 reviewers suggest that it is accepted or is that up to the editor? What is the most common outcome in situation like this?

I'm stuck in this situation now - all 3 reviewers suggested some changes; while 2 were quite optimistic and also said that my work is interesting, the third one was really against it and suggested that we rewrite article anew, thus semi-rejected it; the reviewer really seems determined that our paper should not be accepted. Now, if I waste 2 months applying the changes and then wait another 3 for a review only to find out that the third reviewer still thinks it should be rejected, I've wasted almost half a year on nothing. The alternative is to resend the article to some other journal. 

Any kind of feedback will be appreciated.
To add to the other answer that has been posted so far (from @mgbdog), I will add something from the perspective of a past editor in chief and frequent associate editor, in the field of computer science.

Generally (look at the various posts about journal workflows) the reviewers provide their feedback, an associate editor recommends, and then the editor in chief either accepts that recommendation or pushes back in some fashion.  So the comment about the editor wanting to proceed is accurate in the aggregate but it really refers to multiple people as a rule.

But the key here is that when one reviewer recommends rejection, and other reviewers were more positive, I assume that the OP received a "major" revision that means it will be returned to the reviewers.  Two already liked it so probably will be in favor of the revision.  The one who didn't, if they were negative enough, may actually not be willing to review it again (after all, the editors didn't listen to them).  I have encountered that several times as an associate editor.  If they do review it again, hopefully they are professional about it and read it with an open mind, and you get your opportunity to improve the paper and respond to their criticism.  So hopefully they are convinced to accept.  

If they don't, there is indeed a chance the paper will be rejected after revision.  Just because the editor said to give you a chance to fix it doesn't mean in the end it is fixable.  I once did a journal article that got back extensive comments and a very specific warning from the associate editor that we should not assume that the major revision we were doing actually would result in acceptance. We must have worked on it for another year and with a second minor revision after that, but it got in.  

But just because it may be rejected doesn't mean you shouldn't try.  With 2/3 favorable, you seem well positioned, as long as you believe you can either fix what #3 complained about or make an argument in your response that they have it wrong.