Hello Elke, hello Markus,
thank you for your advices.
When I have drew simple diagram with locked tables (Select * from locks)
and processes waiting for lock release (Select * from lock_waits),
I found a loop (cycle). It seems, when I changed tables
access order, everything is running OK.
Thank you for your support.
Regards, Dusan
Dusan Kolesar
Helsinska 19
040 13 Kosice
Slovakia
e-mail primary : [EMAIL PROTECTED]
e-mail alternative : [EMAIL PROTECTED]
ICQ# : 160507424
On Wed, 15 Mar 2006 13:16:57 +0100, Zabach, Elke <[EMAIL PROTECTED]>
wrote:
Dusan Kolesar wrote:
Hello Markus
So, the database is waiting for a lock release.
But what can cause this datatbase state ?
Can you advice me some DB parameters to change ?
Are there some parameters to unlock tables automaticly ?
Or should I change something in my procedure and trigger code ?
Dusan Kolesar
Helsinska 19
040 13 Kosice
Slovakia
e-mail primary : [EMAIL PROTECTED]
e-mail alternative : [EMAIL PROTECTED]
ICQ# : 160507424
On Tue, 14 Mar 2006 14:20:54 +0100, Döhr, Markus ICC-H
<[EMAIL PROTECTED]> wrote:
>> my database has freezed again.
>> See attachement for x_cons output.
>> Can you describe me what does it mean ?
>> Where should I search for problem ?
>
> ID UKT UNIX TASK APPL Current Timeout Region
Wait
> tid type pid state priority cnt try
item
> T135 7 -1 User 3992* Vwait 0 0
252
> 36171(s)
> T136 7 -1 User 968* Vwait 0 0
251
> 36171(s)
> T139 7 -1 User 3544* Vwait 0 0
252
> 36171(s)
> T140 7 -1 User 97304* Vwait 0 0
46
> 36171(s)
>
>
If you think that your database is blocked for a long time, please check
database parameter REQUEST_TIMEOUT. Perhaps this has a high number (of
seconds) a task is willing to wait for the lock it wants, but which is
locked by another task.
Sometimes deadlocks can be detected and one of the tasks will be
rollbacked implicitly. Sometime a deadlock will not be detected and
several tasks are blocked until at least one of them reaches its
REQUEST_TIMEOUT-time.
On the other hand you can check the item (table/row) which causes this
blocking.
Select * from locks (done by the sysdba) will show all locks hold by any
task.
Select * from lock_waits will tell you the requests (R_...) the tasks
have and which lock_holder (H_...) causes the request to wait and (last
column) how long its remaining waiting time is.
With this lock_waits-output, I assume you will find some cycle, meaning
task1 is waiting for task2 which itself is waiting for task3, which, to
make things worse is waiting for task4 which is waiting (full cycle) for
something hold by task1. So noone can receive the lock he wants unless
at least one tasks releases its hold locks.
If you then know the objects causing the trouble, you should check your
application(s) working with these objects and if one of them first locks
objects in table A and then those in B and the other application does
this the other way round. This then causes deadlocks. Please keep
foreign keys in mind which work with more than one table and the
isolation level used in your application(s).
Good luck.
Elke
SAP Labs Berlin
> The database is waiting for a lock release. All of those application
PIDs
> are waiting for the item 36171.
>
> Unfortunately I don't know how to find out via command line what
exactly
> this lock is.
>
> Anyone of the development can give a hint here?
>
>
> Greetz,
>
>
> SIEGENIA-AUBI KG
> Informationswesen
> i.A.
> Markus Döhr
> SAP-CC/BC, SAPDB-DBA
>
> Tel.: +49 6503 917-152
> Fax: +49 6503 917-7152
> E-Mail: [EMAIL PROTECTED]
> Internet: http://www.siegenia-aubi.com
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
