why does transaction roll back on RuntimeException but not SQLException

This is defined behaviour. From the docs:

Any RuntimeException triggers rollback, and any checked Exception does not.

This is common behaviour across all Spring transaction APIs. By default, if a RuntimeException is thrown from within the transactional code, the transaction will be rolled back. If a checked exception (i.e. not a RuntimeException) is thrown, then the transaction will not be rolled back.

The rationale behind this is that RuntimeException classes are generally taken by Spring to denote unrecoverable error conditions.

This behaviour can be changed from the default, if you wish to do so, but how to do this depends on how you use the Spring API, and how you set up your transaction manager.