ac.commutative algebra - Differences between reflexives and projectives modules

Well, the answer is well known of course. For a finitely generated module over a commutative normal Noetherian domain TFAE

1. M is reflexive


2. M is torsion-free and equals the intersection of its localizations at the codimension 1 primes


3. M satisfies Serre's condition S2 and its support is Spec R.


4. M is the dual of a f.g. module N

As you say, a finite projective module is the same as a locally free sheaf on Spec R. Similarly, a finite reflexive module is the same as the push forward of a locally free sheaf from an open subset U of Spec R whose complement has codimension $ge2$.

So for an easy example take a Weil divisor D which is not Cartier, the associated divisorial sheaf (corresponding to an R-module of rank 1) is reflexive, not projective. Your example with a line on a quadratic cone is of this form.

This stuff is standard and used all the time in higher-dimensional algebraic geometry around the Minimal Model Program. For an old reference covering some of this, see e.g. Bourbaki, Chap.7 Algebre commutative, VII.