For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense):
DML statements against table variables (variable, even table variables, do not ...
It depends on the transaction isolation level.
In read committed it will release locks.
In "Repeatable read" and "Serializable" it will hold locks.
You can find more info in the following article: SET TRANSACTION ISOLATION LEVEL
is it true that all data modifications are undone when the transaction they are a part of
gets rolled back?
Yes. That is the WHOLE sense of a transaction.
For example if a cursor that just executed a stored procedure 100 times that updated a
table on each iteration, would all those updates get rolled back?
That is the definition of all changes.
For SQL Server:
But what's about single statements?
In SQL Server, there is 4 transaction isolation levels (in pessimistic locking model). Default transaction isolation level is Read Committed, and locks are placed on a statement level. If you have a transaction that has 2 statements inside it that retrieve same data, and in the middle of that ...
I've just run some tests, here are results:
Drop Database cannot be performed within explicit (begin tran...) transaction
SQL Server throws:
DROP DATABASE statement cannot be used inside a user transaction.
Drop table you can do it within explicit transaction without any problem
Create View - you can do it, but first have to run begin tran..., then run ...