Hi and thanks for your reply.
I'm still trying to determine what caused the deadlock
and for that porpouse I'm examining the output of
InnoDB monitor and found this
LATEST DETECTED DEADLOCK
------------------------
050311 15:43:08
*** (1) TRANSACTION:
TRANSACTION 0 735264031, ACTIVE 1 sec, process no 10377, OS thread id
22401037 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 161 lock struct(s), heap size 11584, undo log entries 11
MySQL thread id 5461, query id 364730 localhost root Updating
update pr_prestaciones set n_comp_caja_af='9751' where idprestacion in
(0,256599)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57326 n bits 104 index `PRIMARY` of
table `hrrg/pr_prestaciones` trx id 0 735264031 lock_mode
X locks gap before rec waiting
........
It appears that *** (1): was waiting for a lock to be granted and that
*** (2) here below was holding the lock
*** (2) TRANSACTION:
TRANSACTION 0 735264030, ACTIVE 2 sec, process no 10376, OS thread id
22396940 fetching rows, thread declared inside InnoDB 1
80
mysql tables in use 9, locked 9
7627 lock struct(s), heap size 437568
MySQL thread id 5460, query id 364663 192.168.10.60 jvidal Sorting
result
insert into hrrg_int.mj_tmp_cuencenx SELECT pr.idorden as 'Orden',
pr.idprestacion as 'Presta', pr.F_realizacion as 'FechaR'
, pr.f_turno as 'FechaT' , ob.nombre as 'Profesional',
(YEAR(pr.F_turno)-YEAR(af.f_nacimiento)) - (RIGHT(date(pr.f_turno),5)<
RIGHT(af.f_nacimiento,5)) as 'Edad', nm.codigo as
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 57326 n bits 104 index `PRIMARY` of
table `hrrg/pr_prestaciones` trx id 0 735264030 lock mode
S
My question is: Is it normal that a query like the one in *** (2) locks
a table in such a way that it does not allow an update for that table
while such query is sorting the results?
the query in ***(2) was READING data from various tables and inserting
them into another table which was created on the fly for that porpouse
Locked table (pr_prestaciones) was not updated by the query in *** (2)
thanks
Mauricio
On Fri, 2005-03-11 at 18:42, Heikki Tuuri wrote:
> Mauricio,
>
> ----- Original Message -----
> From: "Mauricio Pellegrini" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Friday, March 11, 2005 9:13 PM
> Subject: Lock timeout problem
>
>
> > Hi, we are using mysql 4.1.4-gamma and a few days ago we started to
> > receive lock wait timeout messages from within our application also on
> > other connections via tcp/ip ( from mysql clients ) the same thing
> > happens
> >
> > While we are trying to discover what is causing these errors I've noted
> > this in the output of SHOW ENGINE INNODB STATUS
> >
> > this is the text:
> >
> > ---TRANSACTION 0 735105891, ACTIVE 1812 sec, process no 24059, OS thread
> > id 1741492237
> > 225 lock struct(s), heap size 27968, undo log entries 12427
>
> use SHOW PROCESSLIST to determine if you have a dangling session. You should
> commit your transaction. It looks like someone forgot to commit a big
> transaction.
>
> > Question #1.
> > Can I identify the query that was running in that transaction?
> >
> > I Think it may not be inside the binary log as we had to stop the server
> > and start it again .
> >
> > Question #2.
> > What does the " 225 lock struct(s) " mean
>
> It means appromately that the transaction has locks on 225 pages or 225
> tables.
>
> > Thanks
> > Mauricio
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> Foreign keys, transactions, and row level locking for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
> tables
> http://www.innodb.com/order.php
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
