MySQL Forums
Forum List  »  Newbie

Row level locking vs. table level locking
Posted by: Trupti Kulkarni
Date: June 17, 2010 07:51AM

Hi all,

This is a debatable topic - and I am quite confused as to what should I go for: row-level locking, or table-level locking. Below is a brief explanation:

1. My database has MyISAM tables. There are 12 tables in total, only 4 of which get updated/have new rows inserted often. The rest are more or less static tables; i.e., 1 row is added or updated in 6 months (or even later).

2. 80% of the operations on the 4 main tables are inserts and updates, while rest are simple selects (no GROUP BY, ORDER BY etc.)

If I go for table-level locking:

1. All my tables are already MyISAM, so no change needed there.
2. If I lock a table whilst an operation, no other user can do any other operation on it, even though I am only, lets say, updating one row.
3. Because of the lock, other users will have to wait until the operation is completed and lock is released (basically, my point in #2). And considering inserts are fairly regular, this is kind of a bottleneck.
4. I have some experience in table-level locking, which is always good.

If I go for row-level locking:

1. I need to change all my tables to InnoDB engine.
2. I can lock only one row that is being modified, allowing other users to do other operations, and the new inserts can also take place at the same time on the table.
3. I have no experience whatsoever in row-level locking implementation, which is a main deterrent.

So, can you please guide me on which locking method to use? If you too, like me, lean towards row-level locking, then can you please guide me to some use cases or case studies that have handled this well? I'd much appreciate it.

Thanks,
Trupti

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.