CODEWITHSUNDEEP
mysqldatabaseqtlocking
mekkanizer
mekkanizer

Reputation: 741

Simple read-only locking mechanism

I'm developing a GUI client for MySQL database using Qt.
GUI consists of several "editors" placed in forms, an editor for table X,Y,Z,etc. If I need to prevent the users of client from "collective-editing", how do I go about it the simplest? I just couldn't think of a phrase to google to see other people's solutions.
What I see (as a probable solution) is an in_use table, where I store the locked table name (in_use.table) and the locked row ID (in_use.id).
Each time an editor is opened, a "lock" is inserted, each time it's closed - a lock is removed.
What are the potential pitfalls?

Upvotes: 1

Views: 96

Answers (1)

Crutches
Crutches

Reputation: 179

If you want to lock the table, you can use LOCK TABLE table_name READ;

You can also create locks using GET_LOCK, check if they're open using IS FREE LOCK, and release them using and RELEASE_LOCK.

When an editor is opened, you can check whether your manual lock is in use or not by using GET_LOCK or IS FREE LOCK. After your editor is done, you can use RELEASE_LOCK to free up the next person from accessing the editor.

PITFALLS: Doing this on the application side presents a couple problems. If the user closes the form, how will you tell the database to release those locks? Example: I am your user and I go to your site/fat client and I click edit to start my work, halfway through my computer crashes and shuts down.

EDIT: you can create a number of different locks if you have different use cases and do not want to lock the resources themselves.

See more here, here, and here

Upvotes: 1

Related Questions