Well, personally, I would just put it under DBIx::Transaction. It's just a syntactic sugar module, and doesn't actually implement any kind of transactions. Calling it Transactions could be misleading to people.
About the implementation:
This sample code doesn't actually work, does it? The use transactions part would be executed at compile time before $dbh has a value.
What happens in long running environments like mod_perl when $dbh changes to a new database handle (because of timeout and reconnect with Apache::DBI)? If you only pass in $dbh at compile time (because it's done with a use), the $dbh inside transactions will never get updated.
Defining your subs inline like that looks pretty strange.
I don't think you need to take a reference to $_[1], since it is a ref already.
Creating a routine that takes sub refs but does not appear to (like transaction()) leads to many scoping bugs. People will not understand why return $foo inside of a transaction block doesn't actually return. They will create closures that leak memory by accident. (See the info about Error.pm on this site and elsewhere.) I suggest using a source filter instead. Look at Filter::Simple to see how it can be done.