In a Sybase 12 Stored procedure, what is the scope of

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

in the following. Does it apply to both of the select statements?

CREATE procedure (@param1 int = NULL,
@param2 int= NULL)
as
BEGIN

IF (@param1 IS NULL) or (@param2 IS NULL)
begin
print 'Parameters not entered correctly!'
return -1
end

begin
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM source1 into tempDB.tempTable
INSERT INTO tempDb.tempTable SELECT * from source2
end
end

Hi Pat,

Yes, the scope applys to both (and any subsequent queries) after you
issue the "set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" command.
If you want to limit the scope of the altered transaction isolation
level to a single query then you can issue the query as:

"SELECT * FROM source1 into tempDB.tempTable at isolation read
uncommitted"

Also, be aware that in order for the dirty reads to happen on a table
there must be a unique index on that table.

Hope this help!!

Cheers,

Sara ...

Pat wrote:
> In a Sybase 12 Stored procedure, what is the scope of
>
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>
> in the following. Does it apply to both of the select statements?
>
> CREATE procedure (@param1 int = NULL,
> @param2 int= NULL)
> as
> BEGIN
>
> IF (@param1 IS NULL) or (@param2 IS NULL)
> begin
> print 'Parameters not entered correctly!'
> return -1
> end
>
> begin
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SELECT * FROM source1 into tempDB.tempTable
> INSERT INTO tempDb.tempTable SELECT * from source2
> end
> end

I have to disagree here, at least for the ASE product - the READ
UNCOMMITTED cannot apply to a statement doing data modification (i.e.
select into or insert); the statement will be processed at the next
higher isolation level.

-bret

DBAGAL wrote:
> Hi Pat,
>
> Yes, the scope applys to both (and any subsequent queries) after you
> issue the "set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" command.
> If you want to limit the scope of the altered transaction isolation
> level to a single query then you can issue the query as:
>
> "SELECT * FROM source1 into tempDB.tempTable at isolation read
> uncommitted"
>
> Also, be aware that in order for the dirty reads to happen on a table
> there must be a unique index on that table.
>
> Hope this help!!
>
> Cheers,
>
> Sara ...
>
> Pat wrote:
> > In a Sybase 12 Stored procedure, what is the scope of
> >
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >
> > in the following. Does it apply to both of the select statements?
> >
> > CREATE procedure (@param1 int = NULL,
> > @param2 int= NULL)
> > as
> > BEGIN
> >
> > IF (@param1 IS NULL) or (@param2 IS NULL)
> > begin
> > print 'Parameters not entered correctly!'
> > return -1
> > end
> >
> > begin
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > SELECT * FROM source1 into tempDB.tempTable
> > INSERT INTO tempDb.tempTable SELECT * from source2
> > end
> > end

Thanks Bret ... I missed that caveat in the docs but it makes sense ...

Cheers,

Sara ...

bret@sybase.com wrote:
> I have to disagree here, at least for the ASE product - the READ
> UNCOMMITTED cannot apply to a statement doing data modification (i.e.
> select into or insert); the statement will be processed at the next
> higher isolation level.
>
> -bret
>
> DBAGAL wrote:
> > Hi Pat,
> >
> > Yes, the scope applys to both (and any subsequent queries) after you
> > issue the "set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" command.
> > If you want to limit the scope of the altered transaction isolation
> > level to a single query then you can issue the query as:
> >
> > "SELECT * FROM source1 into tempDB.tempTable at isolation read
> > uncommitted"
> >
> > Also, be aware that in order for the dirty reads to happen on a table
> > there must be a unique index on that table.
> >
> > Hope this help!!
> >
> > Cheers,
> >
> > Sara ...
> >
> > Pat wrote:
> > > In a Sybase 12 Stored procedure, what is the scope of
> > >
> > > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > >
> > > in the following. Does it apply to both of the select statements?
> > >
> > > CREATE procedure (@param1 int = NULL,
> > > @param2 int= NULL)
> > > as
> > > BEGIN
> > >
> > > IF (@param1 IS NULL) or (@param2 IS NULL)
> > > begin
> > > print 'Parameters not entered correctly!'
> > > return -1
> > > end
> > >
> > > begin
> > > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > > SELECT * FROM source1 into tempDB.tempTable
> > > INSERT INTO tempDb.tempTable SELECT * from source2
> > > end
> > > end