First the easy question. SQLRollback() will cause the transaction to be rollbacked. Any changes that were made since the transaction was started, will be undo.
I don't forsee and major problems with queries being submitted from multiple modules. Just make sure that each module uses the same connection handle. Maybe pass the connection handle to each module when it's called by the driver.
Now for the stickler - SQL Server will maintain exclusive locks until the transaction is terminated. If your process takes a long time, you may want to run it during down or slow time.
-Mike