DBD::mysql::st execute failed: Deadlock found when trying to getlock;

After testing one of my scripts on production servers (quad xeon's, 4
3ghz cpu x 2),
we noticed that the script was taking too long when doing DB
transactions. So my
boss asked me to rewrite the db part, to have many children updating the
DB instead
of 1 parent. He thought this would make things faster and make more use
of the
massive hardware. After i rewrote the code I ran into this problem:

DBD::mysql::st execute failed: Deadlock found when trying to get lock;
Try restarting transaction.

There are 8 tables in total, but i made it so they only update 1 to try
and narrow the problem
down. There are 2 children currently updating the same table, and they
are NOT updating the same
rows using the same keys. In other words, no two children ever
update/insert the same data. Can
anyone shed some light on this matter, I have never ran into deadlocks
before. Maybe the logic I
am using is wrong or something. Any input would be appreciated.

Thanks.



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql::st execute failed: Deadlock found when trying toget lock;

G'day mayuran,
Some questions to narrow the field of possiblities. Is this in a
web-server/apache type environment or are you forking multiple processes
yourself? If you are using a webserver, are you using mod_perl or
mod_cgi? What version of mysql are you using? What sort of locking
statements are you using in your code? What makes you think that the
database is wrong in telling you that (at least) 2 children are trying
to lock the same records?
uru
-Dave

mayuran wrote:
> After testing one of my scripts on production servers (quad xeon's, 4
> 3ghz cpu x 2),
> we noticed that the script was taking too long when doing DB
> transactions. So my
> boss asked me to rewrite the db part, to have many children updating the
> DB instead
> of 1 parent. He thought this would make things faster and make more use
> of the
> massive hardware. After i rewrote the code I ran into this problem:
>
> DBD::mysql::st execute failed: Deadlock found when trying to get lock;
> Try restarting transaction.
>
> There are 8 tables in total, but i made it so they only update 1 to try
> and narrow the problem
> down. There are 2 children currently updating the same table, and they
> are NOT updating the same
> rows using the same keys. In other words, no two children ever
> update/insert the same data. Can
> anyone shed some light on this matter, I have never ran into deadlocks
> before. Maybe the logic I
> am using is wrong or something. Any input would be appreciated.
>
> Thanks.
>
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql::st execute failed: Deadlock found when trying toget lock;

G'day mayuran,
Some questions to narrow the field of possiblities. Is this in a
web-server/apache type environment or are you forking multiple processes
yourself? If you are using a webserver, are you using mod_perl or
mod_cgi? What version of mysql are you using? What sort of locking
statements are you using in your code? What makes you think that the
database is wrong in telling you that (at least) 2 children are trying
to lock the same records?
uru
-Dave

mayuran wrote:
> After testing one of my scripts on production servers (quad xeon's, 4
> 3ghz cpu x 2),
> we noticed that the script was taking too long when doing DB
> transactions. So my
> boss asked me to rewrite the db part, to have many children updating the
> DB instead
> of 1 parent. He thought this would make things faster and make more use
> of the
> massive hardware. After i rewrote the code I ran into this problem:
>
> DBD::mysql::st execute failed: Deadlock found when trying to get lock;
> Try restarting transaction.
>
> There are 8 tables in total, but i made it so they only update 1 to try
> and narrow the problem
> down. There are 2 children currently updating the same table, and they
> are NOT updating the same
> rows using the same keys. In other words, no two children ever
> update/insert the same data. Can
> anyone shed some light on this matter, I have never ran into deadlocks
> before. Maybe the logic I
> am using is wrong or something. Any input would be appreciated.
>
> Thanks.
>
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql::st execute failed: Deadlock found when trying toget lock;

David Dick wrote:

> G'day mayuran,
> Some questions to narrow the field of possiblities. Is this in a
> web-server/apache type environment or are you forking multiple
> processes yourself? If you are using a webserver, are you using
> mod_perl or mod_cgi? What version of mysql are you using? What sort of
> locking statements are you using in your code? What makes you think
> that the database is wrong in telling you that (at least) 2 children
> are trying to lock the same records?
> uru
> -Dave
>
> mayuran wrote:
>
>> After testing one of my scripts on production servers (quad xeon's, 4
>> 3ghz cpu x 2),
>> we noticed that the script was taking too long when doing DB
>> transactions. So my
>> boss asked me to rewrite the db part, to have many children updating
>> the DB instead
>> of 1 parent. He thought this would make things faster and make more
>> use of the
>> massive hardware. After i rewrote the code I ran into this problem:
>>
>> DBD::mysql::st execute failed: Deadlock found when trying to get
>> lock; Try restarting transaction.
>>
>> There are 8 tables in total, but i made it so they only update 1 to
>> try and narrow the problem
>> down. There are 2 children currently updating the same table, and
>> they are NOT updating the same
>> rows using the same keys. In other words, no two children ever
>> update/insert the same data. Can
>> anyone shed some light on this matter, I have never ran into
>> deadlocks before. Maybe the logic I
>> am using is wrong or something. Any input would be appreciated.
>>
>> Thanks.
>>
>>
>>
>
Ok here is some background info, this is an application that runs on the
back end of
our system. We are running MySQL v4.0.18. My database interactions
work like this:

UPDATE table SET value=value+? WHERE key1=? AND key2=?

if update fails, then INSERT INTO table.

I do it this way because I need to increment data if the keys for it
already exist. Otherwise
I need to make a new row for it.

Ive been able to narrow the problem down. I noticed the deadlock was
occuring when there was no rows
(when the database was empty) and it was trying to do an update. So I
changed the code slightly, it now
works like this:

Instead of checking if update fails, SELECT from table where key1=? etc,
then if that exists then perform
an update, otherwise perform an insert. I realize this is much less
efficient but it seems to have solved
this 'deadlock' issue. Im still not sure WHY this solved it. If anyone
can offer an explanation or a
better way for me to accomplish what I need, I am all ears.

Thank you.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: DBD::mysql::st execute failed: Deadlock found when trying toget lock;

David Dick wrote:

> G'day mayuran,
> Some questions to narrow the field of possiblities. Is this in a
> web-server/apache type environment or are you forking multiple
> processes yourself? If you are using a webserver, are you using
> mod_perl or mod_cgi? What version of mysql are you using? What sort of
> locking statements are you using in your code? What makes you think
> that the database is wrong in telling you that (at least) 2 children
> are trying to lock the same records?
> uru
> -Dave
>
> mayuran wrote:
>
>> After testing one of my scripts on production servers (quad xeon's, 4
>> 3ghz cpu x 2),
>> we noticed that the script was taking too long when doing DB
>> transactions. So my
>> boss asked me to rewrite the db part, to have many children updating
>> the DB instead
>> of 1 parent. He thought this would make things faster and make more
>> use of the
>> massive hardware. After i rewrote the code I ran into this problem:
>>
>> DBD::mysql::st execute failed: Deadlock found when trying to get
>> lock; Try restarting transaction.
>>
>> There are 8 tables in total, but i made it so they only update 1 to
>> try and narrow the problem
>> down. There are 2 children currently updating the same table, and
>> they are NOT updating the same
>> rows using the same keys. In other words, no two children ever
>> update/insert the same data. Can
>> anyone shed some light on this matter, I have never ran into
>> deadlocks before. Maybe the logic I
>> am using is wrong or something. Any input would be appreciated.
>>
>> Thanks.
>>
>>
>>
>
Ok here is some background info, this is an application that runs on the
back end of
our system. We are running MySQL v4.0.18. My database interactions
work like this:

UPDATE table SET value=value+? WHERE key1=? AND key2=?

if update fails, then INSERT INTO table.

I do it this way because I need to increment data if the keys for it
already exist. Otherwise
I need to make a new row for it.

Ive been able to narrow the problem down. I noticed the deadlock was
occuring when there was no rows
(when the database was empty) and it was trying to do an update. So I
changed the code slightly, it now
works like this:

Instead of checking if update fails, SELECT from table where key1=? etc,
then if that exists then perform
an update, otherwise perform an insert. I realize this is much less
efficient but it seems to have solved
this 'deadlock' issue. Im still not sure WHY this solved it. If anyone
can offer an explanation or a
better way for me to accomplish what I need, I am all ears.

Thank you.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org