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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
