Add explicit table locking

[PavelTurk]

PostgreSQL, Oracle and other RDBMS allow to lock tables explicitly in different modes. See https://www.postgresql.org/docs/current/sql-lock.html and https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj40506.html .

I suggest to add similar for H2. For example it can be used for copying whole database from H2 to PostgreSQL. There is a question on SO https://stackoverflow.com/questions/31390308/lock-table-with-exclusive-in-h2-database with 4k views that shows it is a rather popular feature.

[katzyn]

What is a purpose of such lock?

1. If you really want to lock everything, you can use the exclusive mode: https://h2database.com/html/commands.html#set_exclusive
2. If you only need a consistent snapshot of your database, you can use non-standard SNAPSHOT or standard SERIALIZABLE isolation level: https://h2database.com/html/commands.html#set_session_characteristics

[PavelTurk]

@katzyn This lock provides fine grained table locking. set_exclusive as I understand locks whole database.

What about SNAPSHOT do you mean SCRIPT TO/RUNSCRIPT FROM?

I need to copy a file database not a memory one. And this DB can be rather big. If I lock whole database using set_exclusive then database will not be accessible to other users for a long time. If I used lock per table I could unlock table when I copied it.

[katzyn]

I mean

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SNAPSHOT;

---

Anyway, the fastest way to copy currently used persistent database is to use the BACKUP command. It creates a ZIP archive with database and this command can be used safely when database is online. After its execution you can unpack this archive into new place.

[PavelTurk]

@katzyn Thank you very much for your suggestions. But I need to copy from H2 to PostgreSQL. And I found only one way to do it - using JDBC connection. But other words I implement migrate function from H2 to PSQL. I can't backup as I will get H2 code, not PSQL.

[katzyn]

In that case use the mentioned SNAPSHOT isolation level.

[PavelTurk]

@katzyn Ok. I will try. Thank you very much.

[katzyn]

But everything needs to be copied inside a single transaction to ensure consistency.

[PavelTurk]

@katzyn But if I have several tables with 10.000 ... rows won't be a buggy way to do in single transaction? I don't know what psql will do after insert(10 000) , insert(10 0000) + commit.

Maybe it is better to lock whole h2 database using set_exclusive and after that in psql insert(batch of 1 000 rows) + commit after every 1 000?

[PavelTurk]

@katzyn Sorry, I messed different connections. It is time to sleep. I will try SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SNAPSHOT; tomorrow.

[PavelTurk]

@katzyn I tested:

I have two connections: srcConnection (h2) and destConnection (psql). My code:

 //Point X
var lockStatement = srcConnection.createStatement();
lockStatement.execute("SET EXCLUSIVE 1");
lockStatement.close();
srcConnection.commit();

//here SELECT with srcConnection and INSERT with destConnection.

Thread.sleep(100000000) // Point Y

At Point X I connect to H2 database from IDE. At Point Y I try to do select in my IDE. It doesn't work. I see that connection is locked. So, with SET EXCLUSIVE I can lock whole database.

However, if istead of SET EXCLUSIVE I use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SNAPSHOT then at Point Y I can do select and insert in my IDE. So, SET SESSION... didn't lock whole table.

[katzyn]

Why it should? It only provides you a consistent snapshot of your data, your transaction will not see changes from other transactions.