Hello,
I have another problem with table locks.
I'm using autocommit mode.
When my DB procedure looks like:
create dbproc proc1 (IN value Integer) AS
begin
select id into :myid from t1 where col = :value;
insert t2 (col) values (:myid);
end;
Is lock on table t1 released when insert statement is proceeding ?
Or all locks are not released until end of procedure ?
Documentation says:
A lock is released implicitly at the end of the transaction
by the database system or it is released explicitly by an
UNLOCK statement executed by the user.
So, where is end of transaction in autocommit mode ?
Transaction ends when select / insert statement ends,
or when whole procedure / trigger ends ?
Thannks for help
--
Dusan Kolesar
Helsinska 19
040 13 Kosice
Slovakia
e-mail primary : [EMAIL PROTECTED]
e-mail alternative : [EMAIL PROTECTED]
ICQ# : 160507424
On Mon, 20 Mar 2006 08:50:05 +0100, Dusan Kolesar <[EMAIL PROTECTED]>
wrote:
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]