Questions about MVCC

[Gili]

Hi,

Looking at http://www.h2database.com/html/advanced.html#mvcc,

1. Does H2 only support table locks or MVCC? Or is there a way to get row locks without MVCC?
2. Using MVCC, do insert/delete operations wait until all open transactions complete?
3. Does this imply that open transactions will never experience insert/delete rows by other threads in mid-transaction?
4. What happens in the following scenario?

Database contains a single row: count[value=1]

T1: Open transaction

T2: Open transaction

T2: update count set value=2 where value=1

T2: commit

T1: select value from count

Will T1 see a value of 1 or 2?

Thanks,

Gili

[Thomas Mueller]

Hi,

H2 only supports row level locks when using the MVCC mode. By the way, the plan is that in the future (with the MVStore), the MVCC mode will be the default mode.

Insert/delete operations don't wait for other operations that do not conflict. They wait for conflicting operations (changes on the same rows).

> What happens in the following scenario?

H2 uses "read committed" by default, so T1 will see the value from T2.

Regards,

Thomas

--
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/groups/opt_out.
 
 

[cowwoc]

    Thanks Thomas. So you're saying I can use other isolation modes with MVCC as I normally would?

Gili

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Gili]

Thomas,

Can you please clarify how H2's implementation of MVCC interacts with Transaction Isolation?

1. Is there a practical difference between READ_COMMITTED and REPEATABLE_READ in MVCC mode? Doesn't MVCC prevent READ_COMMITTED from seeing updates committed by other transactions (after the current transaction has already read the row once)? If so, aren't they identical?
2. Same question for REPEATABLE_READ and SERIALIZABLE isolation in MVCC mode.
3. Does H2 implement http://en.wikipedia.org/wiki/Serializable_Snapshot_Isolation#Serializable_Snapshot_Isolation for MVCC? If not, do you plan on doing so in the future?

Thank you,

Gili

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@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/groups/opt_out.
 
 

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi

Sorry for the delay.

> Is there a practical difference between READ_COMMITTED and REPEATABLE_READ in MVCC mode? 

H2 only supports READ_COMMITTED currently, as documented in http://h2database.com/html/advanced.html?highlight=Mvcc&search=MVCC#mvcc "Connections only 'see' committed data, and own changes. That means, if connection A updates a row but doesn't commit this change yet, connection B will see the old value. Only when the change is committed, the new value is visible by other connections (read committed)".

> Does H2 implement Serializable Snapshot

Currently not, and I didn't plan to support it. In theory, it could be supported with the MVStore, but I don't currently think it's very important.

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[cowwoc]

Hi Thomas,

Thanks for the reply.

On 21/11/2013 6:43 AM, Thomas Mueller wrote:

> Does H2 implement Serializable Snapshot

Currently not, and I didn't plan to support it. In theory, it could be supported with the MVStore, but I don't currently think it's very important.

How does SERIALIZABLE isolation in MVCC mode compare to SERIALIZABLE_SNAPSHOT? Aren't they the same thing?

Thanks,
Gili

Regards,

Thomas

Thank you,

Gili

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/groups/opt_out.
 
 

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, 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/groups/opt_out.
 
 

--
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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

> How does SERIALIZABLE isolation in MVCC mode compare to SERIALIZABLE_SNAPSHOT? Aren't they the same thing?

I can only refer to Google, which found this: http://blogs.msdn.com/b/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx

Regards,

Thomas

[cowwoc]

Hi Thomas,

It was my understanding that SERIALIZABLE isolation in MVCC mode is supposed to behave like SERIALIZABLE_SNAPSHOT. At least, that's the case for Postgres. If SERIALIZABLE behaves the way you mention in MVCC mode, what is the difference if MVCC mode is on or off? It doesn't sound like there is a difference.

Gili

[Ryan How]

This is interesting :)

Does MVCC even support different isolation modes?. I thought it was it's own isolation mode? The different isolation modes don't seem to make sense in an MVCC context?

Thanks, Ryan

[Thomas Mueller]

Hi,

> Does MVCC even support different isolation modes?

Up to some point, yes, see http://www.postgresql.org/docs/9.3/static/transaction-iso.html

Regards,

Thomas

[Ryan How]

I get confused. That is talking about 1 transaction seeing another transactions results if it commits the, but http://en.wikipedia.org/wiki/Multiversion_concurrency_control says it looks at the state of the database before the other transaction ever even started.

[Gili]

Thomas,

I just want to make sure that you don't lose track of this question (since you replied to a follow-up discussion): what is the difference between SERIALIZABLE isolation when MVCC is on or off?

Thanks,

Gili

Regards,

Thomas

Thank you,

Gili

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@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/groups/opt_out.
 
 

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@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/groups/opt_out.
 
 

--
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+unsubscribe@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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@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/groups/opt_out.

--
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+unsubscribe@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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

When MVCC is disabled, see http://h2database.com/html/advanced.html#transaction_isolation

When MVCC is enabled, then H2 behaves in the same way as with read committed. At least currently. In the future this may change. See also http://h2database.com/html/advanced.html#mvcc for details

Regards,
Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[cowwoc]

Hi Thomas,

Thanks for the clarification.

What happens when MVCC is on and a user sets the transaction isolation to something other than read committed?

Thanks,
Gili

Regards,

Thomas

Thank you,

Gili

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/groups/opt_out.
 
 

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, 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/groups/opt_out.
 
 

--
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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, 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/groups/opt_out.

--
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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, 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/groups/opt_out.

--
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/groups/opt_out.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/bGNuMpLr8IY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Ryan How]

I think we need to do up some test cases and experiment to see what happens. It isn't very clear to me how MVCC mode behaves. I've made some assumptions, but it seems I may be wrong based on this discussion...

[Wen Bob]

If MVCC is enabled, changing the lock mode (LOCK_MODE) has no effect.

 See also http://h2database.com/html/advanced.html#mvcc for details

在 2013年11月28日星期四UTC+8上午4时01分01秒，Gili写道：

[cowwoc]

Hi Wen,

It's not explicitly clear whether the documentation is referring to the LOCK_MODE variable, or Connection.setTransactionIsolation() as well. Thomas, please clarify (and update the documentation accordingly).

Thanks,
Gili