Deadlock Issue

am 23.02.2005 22:15:17 von Melissa Dougherty

------=_NextPart_000_001E_01C519C2.DD351300
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_001F_01C519C2.DD351300"

------=_NextPart_001_001F_01C519C2.DD351300
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=3D0;

begin;

delete from trans where val=3D'a2';

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

User 2:

set autocommit=3D0;

begin;

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

Melissa A Dougherty
------=_NextPart_001_001F_01C519C2.DD351300
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

charset=3Diso-8859-1">

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

User =
1:

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">set =
autocommit=3D0;

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">begin;

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">delete from trans where=20
val=3D'a2';

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">.....(additional =
statement...=20
INSERTs and/or UPDATEs)

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">User =
2:

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial"> face=3DArial=20
size=3D2>set=20
autocommit=3D0;

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">begin;

size=3D2> style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">insert into trans (val) =
values=20
("b4");

style=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">

Melissa A =
Dougherty

------=_NextPart_001_001F_01C519C2.DD351300--

------=_NextPart_000_001E_01C519C2.DD351300
Content-Type: text/plain; charset=us-ascii

--
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
------=_NextPart_000_001E_01C519C2.DD351300--

Re: Deadlock Issue

am 24.02.2005 13:29:11 von mauriciocperez

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'.

Mauricio.

On Wed, 23 Feb 2005 16:15:17 -0500, Melissa Dougherty
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
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=mauriciocperez@gmail.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