How to lock a table

[Anu Padki]

Hello,

I want to lock only one table, how can I achieve this?
Have some legacy code that creates an id from a table. In order to make sure two processes do not access the same id, I need to lock it.
Can anyone help please?
- Anu

[Thomas Mueller]

Hi,

> I want to lock only one table, how can I achieve this?

Do you want a shared lock (so that others can not update the table,
but can read data)? Or an exclusive lock?

shared lock:
SELECT * FROM TEST FOR UPDATE;

exclusive lock:
UPDATE TEST SET ID=0 WHERE 1=0;

Regards,
Thomas

[Anu Padki]

Hello Thomas,

Thanks for your response.
So do you mean that the update statement will lock the entire table and would not allow even the select? If so , I believe for generating an id from a table (table has just one column and one row), the code is to the effect

select present id,
increase id by 1
update table with new id.

I want the table locked for all the three statements, how do you suggest I achieve this? I could put a pseudo update before the select but then I believe as soon as the update is completed the exclusive lock is removed.
Any other suggestion so that the table is in exclusive lock mode until all the three statements are done? the db is mvcc=true.
Thanks for all your help
- Anu

[Thomas Mueller]

Hi,

I would probably try to use a sequence if I was you.

Sorry I made a mistake: SELECT * FROM TEST FOR UPDATE will also do an
exclusive lock. See also
http://www.h2database.com/html/grammar.html#select "If FOR UPDATE is
specified, the tables are locked for writing."

A shared locks would be just using SELECT, but you first need to call
SET LOCK_MODE 1 - see also
http://www.h2database.com/html/grammar.html#setlockmode

> So do you mean that the update statement will lock the entire table and
> would not allow even the select?

> the three statements are done? the db is mvcc=true.

If you use MVCC then it's different. See
http://www.h2database.com/html/advanced.html#mvcc

> I want the table locked for all the three statements, how do you suggest I
> achieve this?

You could use:

select id from test where ... for update
update test set id = y where ...

Regards,
Thomas

[Anu Padki]

Hello Thomas,

This is the scenario I want to achieve, which I could not with select for update .. or tablemode=1, (db is mvcc= true)

In mysql in one session when I issue a command

lock table mytable write;

Then I open another session and issue
select * from mytable;
This session cannot select from db;

when I got to first session and then run 'unlock tables'
The second session can read from mytable;

This is the scenario I want to achieve on H2.
Is there a way I can do it using H2 db commands?
- Anu

[Thomas Mueller]

Hi,

Multi version concurrency is specially made to avoid blocking readers.
But it looks like you want to explicitly lock readers. So you can't
use Multi-version concurrency. In H2, what you could do is:

Open two connections. On both connections, call:

SET LOCK_TIMEOUT 60 * 1000 (that is one minute, I hope that's enough).

> MySQL: lock table mytable write;

Start a transaction (Connection.setAutoCommit(false) or execute BEGIN).
SELECT * FROM MYTABLE WHERE 1=0 FOR UPDATE;

> Then I open another session and issue
> select * from mytable;

Do the same.

> This session cannot select from db;

When I test it, this connection is blocked.

> when I got to first session and then run 'unlock tables'

In H2, commit the transaction.

Regards,
Thomas