At 05:43 AM 7/29/2006, you wrote:
Dear list,
we are running a Mysql 5.0.22 server that is accessed by a number of
java web applications (jdbc driver 3.1) over the network. The
database layer was realized using the Cayenne framework and InnoDB
was chosen as the table type. All applications share the same
database because they use the same type of objects. These
applications were running fine for some time now, but during the last
weeks a problem came up that occurs randomly from time to time:
duplicate key exceptions.
The Cayenne framework is using a a single table to keep the primary
keys (PK) for its objects. The method accessing this table is
surrounded by lock / unlock commands, so no other thread can read the
same PK from the table while another thread is currently reading and
incrementing the PK. Each thread accessing the table is incrementing
the value by 20 for caching PK values to reduce the load on the
database.
I assume you are using WRITE locks and not READ locks, correct? Because
READ locks won't prevent other threads from reading the table value.
Something like this should work: (I'm using MyISAM but it should still work
with InnoDb.)
lock tables testinc write;
update testinc set rcd_id=rcd_id+1;
select @x:=rcd_id from testinc;
unlock tables;
select @x;
(I only used "@x" because this allows me to see the next rcd_id value in my
GUI, after the Unlock table clears the results.)
When you lose the connection and reconnect, can you not clear the cache for
the autoinc table so it gets the current value?
Mike
What we experience in all of our applications now is duplicate key
error messages by the database. By analyzing the log files we found
out, that different applications were using the same set of PK values
for the objects they were creating. A thing that should not be
possible because of the lock / unlock logic described above. While
different threads do use the same set of PK values only one thread
can "win" the insert statement, the other one will of course receive
a duplicate key error from the database. In those cases the log file
of an application shows 20 exceptions due to the caching mechanism.
However, this error is not always happening, in 99% of the time the
application is running as expected without errors and there is no
rule visible to which we can relate the occurrence of the problem.
Other developers of Cayenne have done some load and concurrency tests
after I reported this problem to the mailing list. But nobody was
able to reproduce the described problem.
In the beginning some of our apps were using "autoReconnect=true" as
an old relict and we thought that might be cause for our problem.
Consider that the connection to the db host is lost for some reason
inside the lock / unlock block, the lock on the table is gone but the
connection is reestablished by the jdbc driver. The Cayenne framework
won't realize the lost connection and continue to read the PK values.
Without the lock another thread can easily read the PK table and
boooom, that would be the cause for our problem. Therefore we removed
all autoReconnect statements so that the jdbc driver would fall back
to its default "autoReconnect=false" and let Cayenne handle the
reconnect itself. But in the end this patch didn't solve the
problem, these error messages still occur.
Does anybody have an idea how to solve this problem? In what
direction could we search next? Is there a switch to let mysql log a
lost db connection? Any hints are appreciated!
Thanks!
Christian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
