Views and Deadlocks

[Karl]

Can a View be used to avoid deadlocks?

I have a trigger that needs to select data from a row of a table that
may have been locked (possibly by the same execution thread that caused
the trigger to fire). I can't set the Isolation level of the Trigger
nor the SQL statement within the trigger(I believe that this is not
allowed).

[Serge Rielau]

Karl wrote:

Can a View be used to avoid deadlocks?

I have a trigger that needs to select data from a row of a table that
may have been locked (possibly by the same execution thread that caused
the trigger to fire). I can't set the Isolation level of the Trigger
nor the SQL statement within the trigger(I believe that this is not
allowed).

Views are simply macros, so the short answer is no.
Howvere your reasoning is flawed. A session cannot lock withitself
unless you have started a new connection (unsupported) through a
backdoor external procedure.
If you really want to use a lower isolation level within teh trigger you
can experiment with a CALL statement in the trigger.
You can then either create the procedure with the appropriate bind
option or use the isolation clause on individual statements.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Karl]

I'm using DB2 Version 7.2.
It was my understanding that a CALL Procedure statement from a trigger
was not allowed in version 7.2 - is this correct??

thankyou very much.

[Knut Stolze]

Karl wrote:

I'm using DB2 Version 7.2.
It was my understanding that a CALL Procedure statement from a trigger
was not allowed in version 7.2 - is this correct??

That's correct.

p.s: V7 is out of service for about 1 year now. Maybe you could consider
upgrading to V8?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Karl]

Does a Connection encapsolate muliple Sessions? - If I were a single
user on a single computer and I executed muliple Update Statements
isn't it possible to get a deadlock?
- What if different tables while being updated tried to read from
eachother to modify the way that they updated?

[Knut Stolze]

Karl wrote:

Does a Connection encapsolate muliple Sessions? - If I were a single
user on a single computer and I executed muliple Update Statements
isn't it possible to get a deadlock?
- What if different tables while being updated tried to read from
eachother to modify the way that they updated?

A session is the same as a connection.

And yes, you can get a deadlock if you establish multiple, concurrent
connections. For locking purposes, it does not matter which user
established a connection. But you can never run into any locking problems
within the same connection. That's just an integral property of locks in
relational database systems.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany