You already have trivial counterexamples for your statement, but perhaps you were thinking of a section whose zero locus is irreducible and dominates $Y$. It is false even with that additional assumption:

Let $Y$ be an arbitrary non-singular surface and $Z$ the blow-up up of a (closed) point on $Y$. Then by construction/definition $Z\subseteq X=Y\times \mathbb P^1$. Since $X$ is non-singular, $Z$ is a Cartier divisor and hence there is a line bundle $\mathscr L$ on $X$ such that $Z$ is the zero locus of a global section of $\mathscr L$. Clearly the projection $f:X\to Y$ is flat, so this is a counterexample with the additional property that the zero locus of the section is irreducible and surjects onto $Y$.

You already have trivial counterexamples for your statement, but perhaps you were thinking of a section whose zero locus is irreducible and dominates $Y$. It is false even with that additional assumption:

Let $Y$ be an arbitrary non-singular surface and $Z$ the blow-up up of a (closed) point on $Y$. Then by construction/definition $Z\subseteq X=Y\times \mathbb P^1$. Since $X$ is non-singular, $Z$ is a Cartier divisor and hence there is a line bundle $\mathscr L$ on $X$ such that $Z$ is the zero locus of a global section of $\mathscr L$. Clearly the projection $f:X\to Y$ is flat, so this is a counterexample with the additional property that the zero locus of the section is irreducible and surjects onto $Y$.

EDIT: Oops, I've just realized that there is one more assumption to check, namely that $f_*\mathscr L$ is locally free, but it is easy to check that that also holds.