Views and Deadlocks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karl - 07 Dec 2005 23:32 GMT

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).

Reply to this Message

Serge Rielau - 08 Dec 2005 02:18 GMT

> Can a View be used to avoid deadlocks?
>
[quoted text clipped - 3 lines]
> 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

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Karl - 08 Dec 2005 20:37 GMT

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.

Reply to this Message

Knut Stolze - 08 Dec 2005 20:51 GMT

> 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?

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Karl - 09 Dec 2005 22:29 GMT

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?

Reply to this Message

Knut Stolze - 12 Dec 2005 07:06 GMT

> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message