The Black and Whites looked to be cruising to victory when they led 30-16 after 65 minutes but the Robins hit back with three late tries, one of them a highly-controversial touchdown, to claim a third win in four games and climb into the top eight of Stobart Super League at the expense of champions Leeds.
Winger Sam Latus sparked the late fightback while scrum-half Michael Dobson scored from what looked like a forward pass before Hodgson, fresh from his four-try feat against Castleford, beat two men on a thrilling 60-metre run to score his second try of the match.
The epic encounter brought the curtain down in memorable fashion on day one of the Magic Weekend, which drew 30,763 fans to the Etihad Stadium in Manchester.
Hull, who now have lost all four Magic Weekend derbies, made the early running and worked themselves into an 18-12 half-time lead thanks to tries from Brett Seymour, Richard Horne and Willie Manu. In reply in the first half, the Robins' Craig Hall and Shannon McDonnell crashed over.
There was a brief momentum shift in the third quarter as the Robins subjected their opponents' defence to intense pressure. Replacement prop Ryan O'Hara was twice held narrowly short and right winger Latus got over the line only to be held up on his back by Martin and Briscoe.
However, the pressure paid off on 50 minutes when Blake Green combined with his half-back partner Dobson to get Hodgson over for his first try to cut the deficit to just two points.
The kicking game of Seymour then appeared to swing the game back in Hull's favour as they added two further tries to open up a 30-16 lead. The impressive Manu drew two defenders to get Tony Martin crashing over before Jordan Turner gathered Seymour's kick to the corner to touch down.
Danny Tickle maintain his unerring accuracy to give his side a 14-point cushion but the gap was soon back to two as the Robins roared back.
Hall got Latus over at the corner and Dobson took a return pass from Rhys Lovegrove that looked suspiciously forward to score his side's fifth try with just eight minutes left. And just a minute remained on the clock when Hodgson went on his thrilling touchline run to break Hull hearts and sent the Robins fans into delirium.
Australian scrum-half Tim Smith scored his first try since making his Wakefield debut three months ago to help his side to a 32-26 victory over Castleford in an 11-try feast.
Castleford thought they had snatched the points when substitute forward Nathan Massey went over three minutes from the end but the try was disallowed by video referee James Child for offside.
Smith then immediately went over for the Wildcats' sixth try to settle the first of seven games at the Etihad Stadium played in energy-sapping sunshine.
The Tigers were hoping for redemption following their Super League record 70-12 humiliation at Hull KR, after which they issued a public apology, and their whole-hearted efforts will have won back much of the supporters' trust.
Nobody tried harder than Rangi Chase, the reigning Man of Steel who made a big impact on his return from a three-match ban, setting up three of his side's five tries but gifting two to his opponents.
Wakefield's win enabled them to avenge a 34-16 home defeat on Good Friday and leapfrog their arch-rivals into 11th place in the table.
Widnes gave a nod to the bright future they crave with the pre-match signing of Huddersfield captain Kevin Brown, but were brought crashing back to their miserable present as they were drubbed 68-4 by Warrington on their Magic Weekend debut.
Rooted to the bottom of the Stobart Super League table by some margin on their top-flight return, the Vikings have done little to convince many they were worthy of the three-year license given to them last year. But they have always insisted their project is a long-term one, as they highlighted by announcing the signing of Brown from 2013.
However, even that could not hide their current deficiencies as Warrington ran in 12 tries to register the biggest winning margin in the history of the Magic Weekend.
It was the kind of Widnes performance that must surely leave Brown wondering if he has done the right thing in agreeing to leave title hopefuls Huddersfield and pen a four-year deal with Denis Betts' club.
Not that Warrington were worried about that as Joel Monaghan scored five tries and Matty Blythe grabbed a hat-trick. Garreth Carvell, Ryan Atkins, Stefan Ratchford and Ben Currie scored the remaining four tries.
Despite the question marks raised over their consistency this year, they simply blew their beleaguered neighbours away to go top of the table ahead of Wigan, who play on Sunday.
Widnes did score the first try of the second half when Kurt Haggerty dived into a gap, but it did very little to stem Warrington's momentum.