Row Level Lock Contention

------=_NextPart_000_003D_01C4D092.8C36A2D0
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_003E_01C4D092.8C36A2D0"


------=_NextPart_001_003E_01C4D092.8C36A2D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I have an InnoDB MySQL database setup with an application that uses =
statements like... "SELECT COL1 FROM TABLE WHERE COL1=3D'X' FOR UPDATE" =
causing a row lock for that record. If the user does not commit or =
cancel the transaction correctly, the row lock does not get cleaned up. =


How can I see that lock and cleanup that lock after a user exits the =
application incorrectly?

Thanks,

Melissa
------=_NextPart_001_003E_01C4D092.8C36A2D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable



charset=3Diso-8859-1">

Re: Row Level Lock Contention

Hello Melissa,

There's a startup variable for the MySQL Server called
innodb_lock_wait_timeout that is the time in seconds to wait before
roll back the transaction and release the lock at the lock table.
Innodb is very smart and can notice deadlocks, but if you have this
problem, it may be an exception and you should set this variable.

Best regards,

On Mon, 22 Nov 2004 12:55:30 -0500, Melissa Dougherty
wrote:
>
> I have an InnoDB MySQL database setup with an application that uses
> statements like... "SELECT COL1 FROM TABLE WHERE COL1='X' FOR UPDATE"
> causing a row lock for that record. If the user does not commit or cancel
> the transaction correctly, the row lock does not get cleaned up.
>
> How can I see that lock and cleanup that lock after a user exits the
> application incorrectly?
>
> Thanks,
>
> Melissa
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>
>

--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Row Level Lock Contention

Yes.... you are right. This is the way it behaves.

The problem is the first query that caused the initial lock. If that user
disconnect incorrectly, the record does not free. How do you see these
locks/free them?



----- Original Message -----
From: "Daniel da Veiga"
To:
Sent: Monday, November 22, 2004 1:02 PM
Subject: Re: Row Level Lock Contention


> Hello Melissa,
>
> There's a startup variable for the MySQL Server called
> innodb_lock_wait_timeout that is the time in seconds to wait before
> roll back the transaction and release the lock at the lock table.
> Innodb is very smart and can notice deadlocks, but if you have this
> problem, it may be an exception and you should set this variable.
>
> Best regards,
>
> On Mon, 22 Nov 2004 12:55:30 -0500, Melissa Dougherty
> wrote:
>>
>> I have an InnoDB MySQL database setup with an application that uses
>> statements like... "SELECT COL1 FROM TABLE WHERE COL1='X' FOR UPDATE"
>> causing a row lock for that record. If the user does not commit or
>> cancel
>> the transaction correctly, the row lock does not get cleaned up.
>>
>> How can I see that lock and cleanup that lock after a user exits the
>> application incorrectly?
>>
>> Thanks,
>>
>> Melissa
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe:
>> http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>>
>>
>
> --
> Daniel da Veiga
> Computer Operator - RS - Brazil
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=melissa@cse-corp.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Row Level Lock Contention

Hello again,

You can use the InnoDD monitors to see what locks are on, this is described at:

http://dev.mysql.com/doc/mysql/en/InnoDB_Monitor.html

I suppose that if you have the transaction index number you can kill
it with no problems... But its always best to tune your software to
avoid deadlocks, there are some tips at:

http://dev.mysql.com/doc/mysql/en/Innodb_deadlocks.html

In section "How to cope with deadlocks".

Hope that helps,

On Mon, 22 Nov 2004 13:19:30 -0500, Melissa Dougherty


wrote:
> Yes.... you are right. This is the way it behaves.
>
> The problem is the first query that caused the initial lock. If that user
> disconnect incorrectly, the record does not free. How do you see these
> locks/free them?
>
>
>
>
> ----- Original Message -----
> From: "Daniel da Veiga"
> To:
> Sent: Monday, November 22, 2004 1:02 PM
> Subject: Re: Row Level Lock Contention
>
> > Hello Melissa,
> >
> > There's a startup variable for the MySQL Server called
> > innodb_lock_wait_timeout that is the time in seconds to wait before
> > roll back the transaction and release the lock at the lock table.
> > Innodb is very smart and can notice deadlocks, but if you have this
> > problem, it may be an exception and you should set this variable.
> >
> > Best regards,
> >
> > On Mon, 22 Nov 2004 12:55:30 -0500, Melissa Dougherty
> > wrote:
> >>
> >> I have an InnoDB MySQL database setup with an application that uses
> >> statements like... "SELECT COL1 FROM TABLE WHERE COL1='X' FOR UPDATE"
> >> causing a row lock for that record. If the user does not commit or
> >> cancel
> >> the transaction correctly, the row lock does not get cleaned up.
> >>
> >> How can I see that lock and cleanup that lock after a user exits the
> >> application incorrectly?
> >>
> >> Thanks,
> >>
> >> Melissa

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org