First of all, make sure that ack is -1 and idempotence is true, because one will get ConfigException, saying that "ack must be all in order to use idempotent producer"

The consumer in the listener container will read the same message again, because consumer position will be reset by method recordAfterRollback of ListenerConsumer, when tx is rolled back on exception.

Commiting offset (via producer's sendOffsetsToTransaction) and sending message via kafka template is done in the same kafka transaction and the same producer instance.

If you're worried about duplicates in the topic, which may occur, set isolation.level for your consumers = read_committed, this will make consumers read only committed messages.
You can read about kafka transactions and how it works here

Also, since you're inserting something in a database, read how to sync jdbc and kafka transactions here. Because KafkaTransactionManager can't cover this alone.