Confuse ! XACT_Abort + Begin Transaction

Hi all.
I'm very confuse.
I was thinking that I understand Stored Procedure, Begin, Commit and
RollBack Transaction, but I think that is false.
I have a stored procedure that is called from a Visual Basic application. My
stored procedure look like that.
Create procedure [sp_Test]
as
Declare @.......
Set nocount on
set xact_abort on
set deadlock priority low
set transaction isolation level serializable
Begin transaction
select ...
update ...
insert ...
update ...
insert ...
commit transaction
Why if an error occur in my procedure, a LOCK continue to exists even if the
procedure is stopped and my Visual Basic application return an error ?
Why the lock continue to exists even if I use SET XACT_ABORT on ?
Thanks
** Sorry for my english.
JonathanIf the procedure produces a result set before the error occurs, SQL Server
may hold onto resources until that result set is processed by the vb app.
The rollback cannot finish executing until the result set is read out. Your
application must always read every result set that is produced by a stored
procedure to completion, even if an error occurred.
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:E4B5CF75-3757-43B1-802E-4131DAFF3FCA@.microsoft.com...
> Hi all.
> I'm very confuse.
> I was thinking that I understand Stored Procedure, Begin, Commit and
> RollBack Transaction, but I think that is false.
> I have a stored procedure that is called from a Visual Basic application.
My
> stored procedure look like that.
> Create procedure [sp_Test]
> as
> Declare @.......
> Set nocount on
> set xact_abort on
> set deadlock priority low
> set transaction isolation level serializable
> Begin transaction
> select ...
> update ...
> insert ...
> update ...
> insert ...
> commit transaction
> Why if an error occur in my procedure, a LOCK continue to exists even if
the
> procedure is stopped and my Visual Basic application return an error ?
> Why the lock continue to exists even if I use SET XACT_ABORT on ?
> Thanks
> ** Sorry for my english.
> Jonathan|||My procedure don't produce any result set.
It's looks like this:
Create procedure ...
as
declare ...
set nocount on
set xact_abort on
set transaction isolation level serializable
set deadlock priority low
begin transaction
if (select count(*) from table1 where ....) = 0
begin
update ...
insert ...
end
else
begin
update ...
insert ...
end
commit transaction
My vb application return an error. I need to close the application to be
able to access the data because if I don't close the application, the data i
s
still locking.
Jonathan
"Brian Selzer" wrote:

> If the procedure produces a result set before the error occurs, SQL Server
> may hold onto resources until that result set is processed by the vb app.
> The rollback cannot finish executing until the result set is read out. Yo
ur
> application must always read every result set that is produced by a stored
> procedure to completion, even if an error occurred.
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:E4B5CF75-3757-43B1-802E-4131DAFF3FCA@.microsoft.com...
> My
> the
>
>|||Could you please supply DDL, sample data, and a code snippet from the
calling application?
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:7B571CEF-1795-455C-83BA-E0BCA7C65527@.microsoft.com...
> My procedure don't produce any result set.
> It's looks like this:
> Create procedure ...
> as
> declare ...
> set nocount on
> set xact_abort on
> set transaction isolation level serializable
> set deadlock priority low
> begin transaction
> if (select count(*) from table1 where ....) = 0
> begin
> update ...
> insert ...
> end
> else
> begin
> update ...
> insert ...
> end
> commit transaction
> My vb application return an error. I need to close the application to be
> able to access the data because if I don't close the application, the data
is
> still locking.
> Jonathan
> "Brian Selzer" wrote:
>
Server
app.
Your
stored
application.
if