Если можно, то переведите сказанное на русский :). Что такое "затык"? Надеюсь, это не то, что я подумал? Может быть это дедлок? Или неспособность разобраться в том, что написано ранее при необходимости дополнить текст процедуры некоторым количетсвом строк на TSQL? Или это просто ошибка времени выполнения? Если ошибка, то приведите пожалуйста ее текст.
Пошагово отлаживать процедуру можно из QA при условии, что вы установите более новую версию SQL-сервера, а именно 2000. Можно отлаживать и в версии 7.0, но в комплекте поставки SQL7.0 подобной утилиты нет. Она поставляется, например, с Visual Studio 6.0.
Блокировки действуют ВСЕГДА. Любое обращение к некоторым таблицы приводит к наложению блокировок (либо явно, либо неявно). Блокировки бывают разделяемые, монопольные, на чтение, на запись, намерения и т.д. (лучше об этом почитать в литературе, а то мой топик превратится в "Войну и Мир"). Внутри одной транзакции блокировки на один и тот же ресурс сохраняются до конца транзакции. Статус блокировки на один и тот же ресурс в рамках одной и той же транзакции может изменяться, то только в сторону захвата еще большей части ресурса, либо ужесточения этой блокировки. Подобный процесс называется "эскалацией блокировок" (например, блокировка на уровне строки может перейти к страничной, а страничная - к блокировке всей таблицы).

Ошибка ошибке рознь. Большинство ошибок проверяется с помощью @@Error. Откат транзакции факт возникновения ошибки автоматом не вызывает, правда это не во всех случаях корректно, для некоторых особых ситуаций требуются особые установки, например set arithabort. Ошибку можно проанализировать и принять ответные меры. Некоторые клиентские инструментарии выявляют ошибку и автоматом посылают rollback. Бороться тогда нужно с ними.
Отдельный вопрос - вложенные транзакции. Суть сводится к тому, что про них можно забыть и рассматривать их как одну большую транзакцию. Внутренние Commit просто опускаешь - потому что в них нет никакого смысла. А вот ЛЮБОЙ rollback откатывает эту макротранзакцию целиком. Зачем так сделано? По одной версии для соблюдения требований атомарности транзакции, по другой версии - так было просто проще.