As everyone has indicated, the screw at the front actually provides the pressure to hold the back part of the muffler in place.

The nut at the back is merely used as lock nut, and doesn't do the real "work".

However, threadlock, etc. is all just a stop-gap measure...

Instead fix the problem altogether by drilling a small screw hole at the junction between the front and the back.

Clean out any debris then tap the hole and insert a locking screw.

No more rotation ever again.