Re: concurrency problem on mysql5?
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 bm, 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]
Re: concurrency problem on mysql5?
Hello! Am 30.07.2006 um 18:40 schrieb mos: 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.) It's also a read lock, no thread can read the table until the the unlock command has been issued. This has been tested in functionality as well as load tests already. When you lose the connection and reconnect, can you not clear the cache for the autoinc table so it gets the current value? The framework does that, after reconnecting it does reread the PK values. The problem must be somewhere else... Christian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concurrency problem on mysql5?
At 12:40 PM 7/30/2006, you wrote: Hello! Am 30.07.2006 um 18:40 schrieb mos: 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.) It's also a read lock, no thread can read the table until the the unlock command has been issued. This has been tested in functionality as well as load tests already. Really?? Here it is from the MySQL manual (horse's mouth): http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from reading or writing the table until the lock has been released. I interpreted that to mean other threads can still read from the table if there is a read lock. A write lock, like the one I used in my sample code that I posted, will prevent others from *reading* the table while someone else is updating it and getting the next rcd_id. This isolates the rcd_id so only 1 thread can read or write to it at a time (the thread can only read it when it is available to be updated-no write lock). I tried my code and it works just fine by forcing the other thread to wait until the other thread's lock has completed. Mike When you lose the connection and reconnect, can you not clear the cache for the autoinc table so it gets the current value? The framework does that, after reconnecting it does reread the PK values. The problem must be somewhere else... 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]
concurrency problem on mysql5?
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. 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 bm, 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]
