Eu entendo as diferenças entre o bloqueio otimista e pessimista *. Agora alguém poderia me explicar quando eu usaria qualquer um em geral?

E a resposta a essa pergunta muda dependendo se estou usando ou não um procedimento armazenado para executar a consulta?

* Mas apenas para verificar, otimista significa "não bloqueie a tabela durante a leitura" e pessimista significa "bloquear a tabela durante a leitura".