What kind of tension can they maintain when the two heroes can't die and stay dead?
Well they do pile on the emotional damage that you would rarely see on a tv show. And they certainly work the deaths with recurring characters that they build up quite nicely and then kill off.
As for the resurrection aspect the boys have each had, each has had significant cost to it, cost that has generally plagued the character for the length of two seasons to four seasons and counting, so their is absolutely consequences to it. And since the show is about myths, resurrection is often tied up into myth stories, quite frequently, so its certainly not outside the scope of the shows genre.
Not to mention the fact that in most shows its extremely rare for the primary cast to die without it being the final episode of that show. So how can the majority of all programming have tension, when you know the lead character is almost 100% assured to be alive?
This shows goes through more characters then most shows do, and the reason for that is to build up characters some short lived, some that have been around for 6 seasons so that death can still always have teeth.