CODEWITHSUNDEEP
mysqlsqldatabasedjangotable-lock
jazzblue
jazzblue

Reputation: 2437

Locking MySQL table at web server level

Suppose I have a web app (using Django on the server side) and I would like one of the users (admin-like user "A") to be able to load a certain view (webpage) where she can modify the database tables which will prevent other users to accessing the database (and views associated with modifying the database) until the user A is finished (presses certain button on the page). I am using MySQL and also SQLite (for development). Here are my questions.

  1. MySQL mechanism of locking tables works on the MySQL server level and not on the web server level. Since the application server (Django) side has only one MySQL user associated with the MySQL server (i.e. all the app users will have the same user on the MySQL side), that mechanism is irrelevant here. Am I correct?

  2. If 1. above is correct, what would be the right way to create such a lock? Well, one could create a separate table "lock_table" which would just have a single Boolean entry and every view would poll it and if it is set - redirect to "db temporarily locked" view. However, I am not sure it is a good way. Please, advise on how to implement it if you know.

Thanks.

Upvotes: 0

Views: 113

Answers (2)

johnbarr
johnbarr

Reputation: 460

Choice number two would be a good bet. Another option would be to have a "virtual second database" defined in your django databases and use this database for executing the lock query. See here for details.

Upvotes: 1

heidi
heidi

Reputation: 665

  1. You're correct. You cannot set a system on the MySQL level for locking tables as every database query will be made by the same MySQL user.

  2. The right way would be to set a lock in cache using memcached or another cache system, and then checking if such a lock existed in a decorator on those views.

Upvotes: 1

Related Questions