/    Sign up×
Community /Pin to ProfileBookmark

Locking MySQL tables…

Copy linkTweet thisAlerts:
Jan 22.2006

Do you bother locking a MySQL table before writing to it or do you just hope for the best that nothing will be corrupted? Does MySQL have default handling for this?

[code=php] mysql_query(“LOCK TABLES mytable WRITE”);
//do writing stuff
mysql_query(“UNLOCK TABLES”) [/code]

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@ShrineDesignsJan 22.2006 — in most cases, you have little to worry about, a safer way to modify multiple rows all at once is to use transactions

locking occurs when you modify a row(s) or table(s), myisam tables lock the entire table whereas innodb tables only lock the row or rows that are being modified

you can use transactions, the BEGIN to start a transaction, you can use ROLLBACK to revert back to before the transaction occured or use COMMIT to end the transaction and save any changes
Copy linkTweet thisAlerts:
@NogDogJan 22.2006 — Note that transactions work with INNODB tables, but with MyISAM tables they are ignored and queries are still processed immediately.
Copy linkTweet thisAlerts:
@bokehauthorJan 22.2006 — locking occurs when you modify a row(s) or table(s), myisam tables lock the entire table whereas innodb tables only lock the row or rows that are being modified[/QUOTE]I read that on the MySQL site [URL=http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html]on this page[/URL] but is that default behaviour? If so what are the locking functions I posted above for? I guess to block read access maybe.
Copy linkTweet thisAlerts:
@NogDogJan 22.2006 — I might consider doing my own explicit locking in a situation where I might be inserting, updating, and/or deleting on multiple tables, where if another process tried to access those tables before I completed all the changes then it would get spurious results. So far, I personally have not run into a situation where I've noticed any table locking problems and had to think/worry about it; but then I haven't worked on any [i]really[/i] complex database appliations in MySQL yet.

Hopefully Chazzy will have some further insights on this when he next checks in.
Copy linkTweet thisAlerts:
@chazzyJan 22.2006 — Hopefully Chazzy will have some further insights on this when he next checks in.[/QUOTE]

Awww how sweet ?

Anyways, MySQL's built in table lock is poor, from a transactional standpoint. You can skip those two statements, unless you don't want to commit until everything's gone through or you can force a rollback.

However, for most projects I really do recommend InnoDB. The most basic feature is that it uses foreign keys. Its transaction model is much different, and what ShrineDesigns said it does, for the most part, boil down to table level locking vs. row level locking.

Typically, I avoid handling transactions on this end of the application. The only times I've needed to do anything along those lines was with some sort of data pumping mechanism. So if you're expecting to have to write to this table with something heavy (1000 inserts at a time sort of thing) go ahead and use the lock or handle your own transaction with begin. then if there's an error at some point, rollback. There's also less that can go wrong if you don't lock the table and the database crashes mid-write than if you're just doing it in a transaction.
Copy linkTweet thisAlerts:
@ShrineDesignsJan 22.2006 — i use myisam for tables that are not modified very often, and i use innodb for tables that get changed frequently or where multiple changes may occur at the same time, while leaving the bulk of the table readable during transactions

myisam even though it is non-transactional, still has its role, and there is benifits to using them over innodb, and the same is true for innodb

using a mixture of the two will give you a robust and fast database for use in web applications
×

Success!

Help @bokeh spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.21,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...