Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


1
(click on this box to dismiss)

How to lock the table?

CUBRID provides database level lock like:

cubrid lockdb demodb

But it locks the entire database.

I have already read Isolation Level related question and CUBRID Transaction and Lock guide.

But Isolation Level, as I understand, locks the table only during the transaction. But I have a series of transactions during which the table should not accept any UPDATE/INSERT/DELETE operations, including the time between these transactions.

What should I do to have the table locked until I manually unlock it?

link comment (1) accepted answer
asked 3 years ago
eye
91
A clarification: the "cubrid lockdb" command doesn't lock the entire database, it only prints the lock status. - [Level:2]stefans 10 months ago
16 Answers
1

There is no way to lock the table so that no write operations are allowed during the specified time. Like you noticed, it is possible to lock the table during the transaction.

It is also possible to configure a CUBRID Broker in Read-only mode to accept only READ operations. But this has to be configured in HA mode, cannot be accomplished in simple one-db mode.

If you are trying to backup your database manually in some programming languages like PHP, it is likely that you do no need to worry about incoming WRITE operations during your backup period. For example, assume you backup your db in blocks of 1000 records via SELECT query. In this case, even if some records from the first 1000 rows are updated while you are retrieving the next 1000 rows, it should not bother you, because those new changes can be backed up in the next backup. The current backup will have all records of this time. This manual retrieval assumes you do full backup, not incremental.

If you want incremental backup during which your tables should be locked, the best solution is to use a Backup feature. It is possible to backup your database online, without stopping your service. You can perform also incremental backup.

link comment (0)
answered 2 years ago
dba
558
1

CUBRID does not allow users to control the LOCKING mechanisms. However, you can set a table to 'read-only' mode  through the use of triggers:

create trigger trg_name before insert on table_name execute reject;
create trigger trg_name before update on table_name execute reject;
create trigger trg_name before delete on table_name execute reject;

You can also use the IF clause of triggers if you only want to set a table in "read-only" mode for everybody except DBA:

create trigger trg_name before insert on table_name if (user() not like '%DBA%') execute reject;
create trigger trg_name before update on table_name  if (user() not like '%DBA%')  execute reject;
create trigger trg_name before delete on table_name  if (user() not like '%DBA%') execute reject;

At the end of the period for which you want tables to be readonly, you can just deactivate/drop the triggers.

link comment (1)
answered 2 years ago
ginarrbrik
516




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: