Locking DB

[WayneK]

Greetings all,

Todays question concerns locking the DB.

I see that mysql DB's can be locked. Is it necessary to use locking?

I have tables that will be holding session info and as such will get hit (we hope) often.

This ends my question of the day.

TIA
[This message has been edited by WayneK (edited 05-26-01@11:14 pm)]

[Terra]

I highly recommend that you do some deep research on this issue - much more than recommendations you may find in here...

MySQL does table locks, and if your table is going to get hit fast and hard - then you could create a perpetual self-defeating backlog if not careful...[nbsp][nbsp]Many people get into trouble over this particular issue, especially Banner Ad scripts...

I've seen them chew up an entire MySQL server by eating all the connection slots and I have to forcibly kill them off and terminate that accounts MySQL privileges...

I can't stress enough to: test test test test test on __your local development system__ before uploading them to a production server...[nbsp][nbsp]Crashing your box is no big deal, however crashing our servers is something that I don't take lightly as 85% of the time it is pure negligence...[nbsp][nbsp]The usual response is: "How was I supposed to know that Script X was going to do that?!?"

WayneK, read the docs (!good info!) and sift through the MySQL mailing list for deeper understandings...[nbsp][nbsp]Somewhere out there may be a 'real world' test case fully documented...

When you find it, feel free to link within this post or possibly write up a tutorial for AOTA.net...[nbsp][nbsp]:)

--
Terra
--Trying to save you a whole lotta trouble later on--
FutureQuest

[Rich]

I would agree with everything Terra has said. From a programmer's viewpoint, application deadlocks from db (or even file) locking problems is about as nasty a plaque as an infinite loop scenario.

Locking is also extremely difficult to properly test and requires your own network of clients and servers to do effectively. (Of course this can be simulted with enough friends on the Internet with Atomic Watches strapped to their wrists. :) )

This means that most locking designs must be implemented with little testing. The production design must be rock-solid.

You will want to understand the following:

-- The locking scope and capabilities of the db you will be using. Some can lock rows or even fields within rows, while others, like mySQL, can only lock at the table level.

-- When and how to lock.

-- How to minimize the time between a lock and an unlock

-- When not to lock. Just because there are multiple accesses to a field in a database, does not mean that it needs to be locked.

-- How to design/re-design tables to minimize the impact of locking.

-- The transaction process. Although the database you use may not support transactions, and you will probably never need to use transactions in your database, it has been my experience that people that fully understand the transaction/comit/rollback process, are capable of tackling the design requirements for basic db locking. I would even encourage you to pretend you are a db engine and write a transaction application (in your favorite language) that implements locking, including comit and rollback functions. (You'll never use this application, of course, because if these functions are not built into the database engine, they cannot be practicably implemented. The exercise will, however, give you the confidence needed to tackle db locking designs).

Rich

[John Lim]

When do you lock a database?

I guess the responses have been quite scary!

So I'll give a technical reply that I hope will settle any confusion.

Locking is done to

a) prevent 2 or more programs from updating the same record in the table simultaneously (done automatically by MySQL anyway - no need to do anything different).

b) Perform a update/delete that affects every record in the table (done automatically by MySQL again).

c) Performing an update/delete that needs to synch with update/delete in another table in a transaction.

If you are holding session info only (1 record for 1 session), then probably each update will be on a different record as it is unlikely that one session could access multiple session records at the same time.

In general, there is no need to do the locking yourself unless your needs are complex (fits into [c] above).

Regards, John
[This message has been edited by John Lim (edited 05-29-01@1:45 pm)]