database management: curing lock issues
Question on best practices on handling SQL database concurrency issues I am pmapping a evaluation to a long list (which calls a computationally intense script) from within clojure. The script itself is designed to be completely free of concurrency side-effects. During the evaluation, several calculations are made that are then written to a SQLite database. My approach is to test for concurrency issues on a dual core system prior to moving to a cluster. What I find is that on occasion there is a database locking issue when sub-processes try to write to the database at the same time (java.sql.SQLException: database is locked). The side effect is that one of the evaluations is not written to the database (bad, cause it takes 3min to compute). I can fix it by catching the exception, and then calling (Thread/sleep) before trying to rewrite again. This is an ugly fix, and I am concerned that this may not scale properly. What is the best practices to handle such an issue in a concurrent and scalable way? Is it as simple as moving to a better database, such as mySQL? I could use Threads/Locks and move the db transaction outside the evaluation loop, or save all transactions and then commit after all evaluations are done. I can't help but feel both solns seem like cheating when working with a conncurrent language such as clojure. Any Advice? I am using contrib.sql and java.sql (org.sqlite.JDBC) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
Isn't programming not all about cheating the computer in doing what you want it to do? In the book programming clojure you can find an example with locks as well. 2010/3/25 Scott sbuck...@gmail.com Question on best practices on handling SQL database concurrency issues I am pmapping a evaluation to a long list (which calls a computationally intense script) from within clojure. The script itself is designed to be completely free of concurrency side-effects. During the evaluation, several calculations are made that are then written to a SQLite database. My approach is to test for concurrency issues on a dual core system prior to moving to a cluster. What I find is that on occasion there is a database locking issue when sub-processes try to write to the database at the same time (java.sql.SQLException: database is locked). The side effect is that one of the evaluations is not written to the database (bad, cause it takes 3min to compute). I can fix it by catching the exception, and then calling (Thread/sleep) before trying to rewrite again. This is an ugly fix, and I am concerned that this may not scale properly. What is the best practices to handle such an issue in a concurrent and scalable way? Is it as simple as moving to a better database, such as mySQL? I could use Threads/Locks and move the db transaction outside the evaluation loop, or save all transactions and then commit after all evaluations are done. I can't help but feel both solns seem like cheating when working with a conncurrent language such as clojure. Any Advice? I am using contrib.sql and java.sql (org.sqlite.JDBC) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.comclojure%2bunsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+ unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- Communication is essential. So we need decent tools when communication is lacking, when language capability is hard to acquire... - http://esperanto.net - http://esperanto-jongeren.nl Linux-user #496644 (http://counter.li.org) - first touch of linux in 2004 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
id prefer best practices if possible typically, cheating has consequences down the line On Mar 25, 10:43 am, Joop Kiefte iko...@gmail.com wrote: Isn't programming not all about cheating the computer in doing what you want it to do? In the book programming clojure you can find an example with locks as well. 2010/3/25 Scott sbuck...@gmail.com Question on best practices on handling SQL database concurrency issues I am pmapping a evaluation to a long list (which calls a computationally intense script) from within clojure. The script itself is designed to be completely free of concurrency side-effects. During the evaluation, several calculations are made that are then written to a SQLite database. My approach is to test for concurrency issues on a dual core system prior to moving to a cluster. What I find is that on occasion there is a database locking issue when sub-processes try to write to the database at the same time (java.sql.SQLException: database is locked). The side effect is that one of the evaluations is not written to the database (bad, cause it takes 3min to compute). I can fix it by catching the exception, and then calling (Thread/sleep) before trying to rewrite again. This is an ugly fix, and I am concerned that this may not scale properly. What is the best practices to handle such an issue in a concurrent and scalable way? Is it as simple as moving to a better database, such as mySQL? I could use Threads/Locks and move the db transaction outside the evaluation loop, or save all transactions and then commit after all evaluations are done. I can't help but feel both solns seem like cheating when working with a conncurrent language such as clojure. Any Advice? I am using contrib.sql and java.sql (org.sqlite.JDBC) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.comclojure%2bunsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+ unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- Communication is essential. So we need decent tools when communication is lacking, when language capability is hard to acquire... -http://esperanto.net -http://esperanto-jongeren.nl Linux-user #496644 (http://counter.li.org) - first touch of linux in 2004 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
Are the writes commutative? Since you are using pmap, I presume so. In that case, you could funnel the writes through an agent serving as a queue. -Per On Thu, Mar 25, 2010 at 9:59 PM, Scott sbuck...@gmail.com wrote: id prefer best practices if possible typically, cheating has consequences down the line On Mar 25, 10:43 am, Joop Kiefte iko...@gmail.com wrote: Isn't programming not all about cheating the computer in doing what you want it to do? In the book programming clojure you can find an example with locks as well. 2010/3/25 Scott sbuck...@gmail.com Question on best practices on handling SQL database concurrency issues I am pmapping a evaluation to a long list (which calls a computationally intense script) from within clojure. The script itself is designed to be completely free of concurrency side-effects. During the evaluation, several calculations are made that are then written to a SQLite database. My approach is to test for concurrency issues on a dual core system prior to moving to a cluster. What I find is that on occasion there is a database locking issue when sub-processes try to write to the database at the same time (java.sql.SQLException: database is locked). The side effect is that one of the evaluations is not written to the database (bad, cause it takes 3min to compute). I can fix it by catching the exception, and then calling (Thread/sleep) before trying to rewrite again. This is an ugly fix, and I am concerned that this may not scale properly. What is the best practices to handle such an issue in a concurrent and scalable way? Is it as simple as moving to a better database, such as mySQL? I could use Threads/Locks and move the db transaction outside the evaluation loop, or save all transactions and then commit after all evaluations are done. I can't help but feel both solns seem like cheating when working with a conncurrent language such as clojure. Any Advice? I am using contrib.sql and java.sql (org.sqlite.JDBC) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.comclojure%2bunsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+ unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- Communication is essential. So we need decent tools when communication is lacking, when language capability is hard to acquire... -http://esperanto.net -http://esperanto-jongeren.nl Linux-user #496644 (http://counter.li.org) - first touch of linux in 2004 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
Hi, maybe you can put finished work packages into a Queue and have a (different) thread reading them from the queue and writing them to the database linearly. So the workers don't have to know about the database and the DB writer doesn't have to care for the computation. Since you can't write to the database in parallel anyway, you don't loose anything, no? See java.util.concurrent for different Queue implementations, as well as CLojure PersistentQueue combined with an atom and watchers or so. (Although I think j.u.c.ArrayBlockingQueue looks interesting for this approach) Disclaimer: I'm not a specialist with this stuff. So take an appropriate amount of salt for evaluation. Sincerely Meikel -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
I would suggest using a more concurrency-aware database. I've had luck with H2 (http://www.h2database.com/). It supports mixed local and remote connections in a thread-safe manner. Additionally it supports simple clustering, sounds like you could have use for such. Best Regards, Joonas -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
Is it as simple as moving to a better database, such as mySQL? PostgreSQL is considerably better (even than MySQL, which still uses locks AFAIK) for anything concurrent. The PostgreSQL is using multiple version concurrency (MVC) approach - the same approach the clojure STM is using. The PostgreSQL might need a bit of tuning (the defaults are very conservative), but after that it usually performs very well. Make sure that you understand the PostgreSQL transactions and how they work, but usually - in default settings, the readers do not block writers, and readers always see consistent view of the data (but this view could be a bit behind in terms of time). The PostgreSQL mailing list is both, friendly and knowledgeable - speaking from experience. Kind regards, Vladimir -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
Re: database management: curing lock issues
thanks for your suggestions two clear options 1) agents and queued transactions 2) MVC enabled databases (postgresql, h2 (neat project)) Ill try the first option and see how it scales, and worst case move to the second Thanks again Scott On Mar 25, 12:47 pm, prhlava prhl...@googlemail.com wrote: Is it as simple as moving to a better database, such as mySQL? PostgreSQL is considerably better (even than MySQL, which still uses locks AFAIK) for anything concurrent. The PostgreSQL is using multiple version concurrency (MVC) approach - the same approach the clojure STM is using. The PostgreSQL might need a bit of tuning (the defaults are very conservative), but after that it usually performs very well. Make sure that you understand the PostgreSQL transactions and how they work, but usually - in default settings, the readers do not block writers, and readers always see consistent view of the data (but this view could be a bit behind in terms of time). The PostgreSQL mailing list is both, friendly and knowledgeable - speaking from experience. Kind regards, Vladimir -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en To unsubscribe from this group, send email to clojure+unsubscribegooglegroups.com or reply to this email with the words REMOVE ME as the subject.
