Re: Deadlock Issue - Melissa Dougherty - com.mysql.lists.win32

3 messages in com.mysql.lists.win32Re: Deadlock Issue

From	Sent On	Attachments
Melissa Dougherty	23 Feb 2005 13:14
Mauricio Pérez	24 Feb 2005 04:28
Melissa Dougherty	24 Feb 2005 05:12

Subject:	Re: Deadlock Issue
From:	Melissa Dougherty (meli...@cse-corp.com)
Date:	02/24/2005 05:12:22 AM
List:	com.mysql.lists.win32

The code turns autocommit on and off throughout the entire application.... for a users.

There are no explicit locks that we have found.

I'm going to test by changing the isolation level to READ COMMITTED. This may be what I'm looking for.

The variable autocommit assigned in that way is only for each user, not for all. You must set autocommit in the config file of the server (for all connections). It's this a problem?

Any other procedures on the same table? You use any explicit lock table?

Activate the server variable 'Lock Wait Timeout'.

On Wed, 23 Feb 2005 16:15:17 -0500, Melissa Dougherty <meli...@cse-corp.com> wrote:

We are having a deadlock issue with a MySQL database application. We have two different users running transactions against the same table (INNODB table type). The first user issues a DELETE statement and the other user issues an INSERT. I thought the DELETE would only issue a row level lock, however, it is locking the table. When the commit from user 1 does not come right away, this causes a problem... does anyone have any suggestions?

User 1:

set autocommit=0;

begin;

delete from trans where val='a2';

.....(additional statement... INSERTs and/or UPDATEs)

User 2:

set autocommit=0;

begin;

insert into trans (val) values ("b4");

Melissa A Dougherty