[h2] MVStore with Serializable Transaction Isolation
Hi, I've been looking for an in-memory key-value database. I couldn't find anything (that was missing deal breaker features), so I spent several days writing my own one, and then realised how hard it was ;). I did some more investigation and thankfully found MVStore. Anyway, I think it provides pretty much everything I need (which is fantastic!). One of the things it doesn't do is something similar to serializable transaction isolation. I would like to get reads of values to either block until the other writing-transactions finish, or fail so I can re-execute at a later time. So, I was wondering, if I wanted to plug this functionality in, whether you guys had any hints on the best way to go about that? I was assuming that since H2 itself supports serializable isolation, that this can be easily implemented on top? Maybe, if you think it's useful, I can even make my code generic enough that it is useful for others, as an optional component for MVStore. I also would like to customise the support for 2-phase commit. I would like to apply my application's optimistic locking checks in the prepare phase (which I can do externally), but after taking write locks in the prepare phase to enforce the contract of these checks on commit. Does that seem possible? Anyway, thanks very much for your work. Kieron Wilkinson -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comjavascript: wrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] MVStore with Serializable Transaction Isolation
Hi Thomas, It sounds like you want something like the TransactionStore utility (org.h2.mvstore.db.TransactionStore), but for serializable transactions: http:// h2database.com/html/mvstore.html#transactionshttp://h2database.com/html/mvstore.html#transactions Yes, exactly. TransactionStore is what I have been playing around with and written my tests against. I've read that page a couple of times, very interesting stuff. Yes, that's what it's doing. But not there are some differences between serializable (what you want) and read committed (what the TransactionStore supports right now) - for details, see http://www.postgresql.org/docs/9.3/static/transaction-iso.html and http://en.wikipedia.org/wiki/Isolation_(database_systems) Thanks for the links. I have read those pages too, plus loads of other trying to gain enough knowledge to write my own, though it's still all a bit new to me, so I'm pretty sure it's not all gone in yet. :) I definitely need to read the Postgres one again. My unit tests against MVStore do indeed indicate the read-committed functionality works as I understood it. One transaction can see other writes as soon as their transactions are committed. And indeed, if that happens, I want to be able to block or force a rollback rather than seeing the old value (which is useful for some other stuff I want to do, but not this particular use case). Please have a look at the docs, and then let's discuss whether you want to extend the current mechanism or write your own. I'm also interested in having serializable transaction isolation for H2 as an option, but would like to keep the current mechanism as the default. Yes, that makes sense. I was hoping I could build on top of what is there, but I wasn't sure how configurable it was. I also gather that a serializable-style of isolation requires a lock per entry, which I guess would add quite a bit of overhead, and we would need to be careful about deadlock, as they would be taken, I suppose, in whatever the reads are happening. Do you think I can build on top, or you do you think this sort of change is quite fundermental to how the current TransactionStore works? Thanks, Kieron On Friday, 18 April 2014 09:43:14 UTC, Thomas Mueller wrote: Hi, It sounds like you want something like the TransactionStore utility (org.h2.mvstore.db.TransactionStore), but for serializable transactions: http://h2database.com/html/mvstore.html#transactions I would like to get reads of values to either block until the other writing-transactions finish, or fail so I can re-execute at a later time. Yes, that's what it's doing. But not there are some differences between serializable (what you want) and read committed (what the TransactionStore supports right now) - for details, see http://www.postgresql.org/docs/9.3/static/transaction-iso.html and http://en.wikipedia.org/wiki/Isolation_(database_systems) Please have a look at the docs, and then let's discuss whether you want to extend the current mechanism or write your own. I'm also interested in having serializable transaction isolation for H2 as an option, but would like to keep the current mechanism as the default. Regards, Thomas On Fri, Apr 18, 2014 at 10:39 AM, Kieron Wilkinson kieron.w...@gmail.comjavascript: wrote: Hi, I've been looking for an in-memory key-value database. I couldn't find anything (that was missing deal breaker features), so I spent several days writing my own one, and then realised how hard it was ;). I did some more investigation and thankfully found MVStore. Anyway, I think it provides pretty much everything I need (which is fantastic!). One of the things it doesn't do is something similar to serializable transaction isolation. I would like to get reads of values to either block until the other writing-transactions finish, or fail so I can re-execute at a later time. So, I was wondering, if I wanted to plug this functionality in, whether you guys had any hints on the best way to go about that? I was assuming that since H2 itself supports serializable isolation, that this can be easily implemented on top? Maybe, if you think it's useful, I can even make my code generic enough that it is useful for others, as an optional component for MVStore. I also would like to customise the support for 2-phase commit. I would like to apply my application's optimistic locking checks in the prepare phase (which I can do externally), but after taking write locks in the prepare phase to enforce the contract of these checks on commit. Does that seem possible? Anyway, thanks very much for your work. Kieron Wilkinson -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to
Re: [h2] 1.4 beta creates much bigger database file
Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] MVStore with Serializable Transaction Isolation
Hi, I want to be able to block or force a rollback rather than seeing the old value It's hard to say what is the best way to implement it. It would be nice if the TransactionStore could be extended. The first thing to do is probably create separate top-level class files, and then possible split TransactionMap into 3 classes: TransactionMapBase, TransactionMapReadCommitted, TransactionMapSerializable. Or something like that. A part of the logic is already there: TransactionMap.trySet, which returns true if the entry could be updated. For the database, I ended up implementing some of the logic in MVSecondaryIndex.add, as there are some complications with what exactly is a duplicate entry. a serializable-style of isolation requires a lock per entry It's possible to implement it that way, but I wouldn't, as it doesn't scale (it would need too much memory and probably get too slow). Instead, I would use just one, or a fixed number of lock objects. The risk is that the thread that is waiting for one particular row is woken up even if the different row is unlocked, so the thread would have to wait again. But that way you don't need much memory. But it depends on the use case. As for the license: if you write your own class (without copying H2 source code), then you can use your own license and don't have to publish the code (but if you want, you can, of course). If you modify an existing H2 class, then you would have to provide or publish those changes (just the changes, not the source code of the rest of your application). Regards, Thomas On Fri, Apr 18, 2014 at 1:28 PM, Kieron Wilkinson kieron.wilkin...@gmail.com wrote: Hi Thomas, It sounds like you want something like the TransactionStore utility (org.h2.mvstore.db.TransactionStore), but for serializable transactions: http:// h2database.com/html/mvstore.html#transactionshttp://h2database.com/html/mvstore.html#transactions Yes, exactly. TransactionStore is what I have been playing around with and written my tests against. I've read that page a couple of times, very interesting stuff. Yes, that's what it's doing. But not there are some differences between serializable (what you want) and read committed (what the TransactionStore supports right now) - for details, see http://www.postgresql.org/docs/9.3/static/transaction-iso.html and http://en.wikipedia.org/wiki/Isolation_(database_systems) Thanks for the links. I have read those pages too, plus loads of other trying to gain enough knowledge to write my own, though it's still all a bit new to me, so I'm pretty sure it's not all gone in yet. :) I definitely need to read the Postgres one again. My unit tests against MVStore do indeed indicate the read-committed functionality works as I understood it. One transaction can see other writes as soon as their transactions are committed. And indeed, if that happens, I want to be able to block or force a rollback rather than seeing the old value (which is useful for some other stuff I want to do, but not this particular use case). Please have a look at the docs, and then let's discuss whether you want to extend the current mechanism or write your own. I'm also interested in having serializable transaction isolation for H2 as an option, but would like to keep the current mechanism as the default. Yes, that makes sense. I was hoping I could build on top of what is there, but I wasn't sure how configurable it was. I also gather that a serializable-style of isolation requires a lock per entry, which I guess would add quite a bit of overhead, and we would need to be careful about deadlock, as they would be taken, I suppose, in whatever the reads are happening. Do you think I can build on top, or you do you think this sort of change is quite fundermental to how the current TransactionStore works? Thanks, Kieron On Friday, 18 April 2014 09:43:14 UTC, Thomas Mueller wrote: Hi, It sounds like you want something like the TransactionStore utility (org.h2.mvstore.db.TransactionStore), but for serializable transactions: http://h2database.com/html/mvstore.html#transactions I would like to get reads of values to either block until the other writing-transactions finish, or fail so I can re-execute at a later time. Yes, that's what it's doing. But not there are some differences between serializable (what you want) and read committed (what the TransactionStore supports right now) - for details, see http://www.postgresql.org/docs/9.3/static/transaction-iso.html and http://en.wikipedia.org/wiki/Isolation_(database_systems) Please have a look at the docs, and then let's discuss whether you want to extend the current mechanism or write your own. I'm also interested in having serializable transaction isolation for H2 as an option, but would like to keep the current mechanism as the default. Regards, Thomas On Fri, Apr 18, 2014 at 10:39 AM, Kieron Wilkinson kieron.w...@gmail.com wrote: Hi,
Re: [h2] Does h2 support a clause like WITH temptab( ) AS ( select ... )
Hi, No, sorry, this is currently not supported. It's hard to say whether it will be supported in the future, it depends on whether many people ask for it. So far chances are low. Except if somebody provides a patch of course :-) Regards, Thomas On Thu, Apr 17, 2014 at 12:29 PM, Мaryia Luskanova mluskan...@gmail.comwrote: if not will it be supported in future releases? Thanks in advance. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comjavascript: wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.pokercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='com.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com javascript:. To post to this group, send email to h2-da...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] [Feature Request] Hybrid mode (if it's not done yet)
Hello, I'd like to request for a mode to run the H2 database to run in memory exclusively, but everything is still written to the H2 db file. This can be done because the programmer has the memory and wants the speed, needs to persist the changes done to the database but does not want to wait. And thank you very much for developing H2. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve.mcl...@gmail.comwrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware. pokercopilot/database/pokercopilot;DATABASE_EVENT_ LISTENER='com.barbarysoftware.pokercopilot.database. DatabaseListener';COMPRESS_LOB=DEFLATE;CACHE_SIZE=65536; RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Mueller wrote: Hi, Probably it's due to the long default retention time, see http://h2database.com/html/grammar.html#set_retention_time - try adding ;retention_time=1000 to the database URL, and tell us if and how much this reduced the size. Regards, Thomas On Thu, Apr 17, 2014 at 3:51 PM, Steve McLeod steve@gmail.comwrote: Hi Thomas, I've tried my desktop app with the new MV store in 1.4.177. After loading a significant amount of data, the database file is 3 times the size, compared to H2 1.3.176 Here are the file sizes, in both cases after the app has stopped: pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes Is that expected? If not, what can I do to help locate the problem? Regards, Steve -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com. To post to this group, send email to h2-da...@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
