MYSQL & Row Level Locking

An anonymous reader noted a press release talking about how NuSphere is planning on adding row level locking to a version of MySQL due out 1Q 2001. Anyone who's used MySQL for anything with more then a few hundred thousand records (like, say, Slashdot to pick an arbitrary example) knows that this is the biggest shortcoming MySQL has... you can work around replication and transactions, but when your whole DB freezes because of one piddley write, its more then just a little annoying.

Comments

Re:MySQL != DB

[tuffy]

MySQL is called a database because people are stupid. My /etc/passwd file is a database. My comma-delimited file of CDs is a database. MySQL is a database management system. Whether or not it's relational is for the pedants to bicker about.

Enough already

[deasmi]

Why is it that MySQL receives so much converage for adding 'features' that most/all other DBMSs take for granted.

It's not even releational yet, supports the references keywork, does nothing. Recently found a MySQL database with duplicate primary keys in a table ???!!

If you need and OpenSource RDBMS PostgreSQL ( Also Open Source ) has had most if not all the important features for years. Yes it may be slightly slower, but it won't corrupt your data randomly, you don't need to take it offline to backup and it supports a proper set of SQL.

But SyBase?

[YuppieScum]

and it seems to me that this should be something 'standard' with any DB that is expected to handle non-trival amounts of data.

Sybase has been the "standard" non-mainframe database in the financial world for many years, storing non-trivial volumes of data, and only in it's most recent version (11.9.2) has it had row-level locking.

In fact, row-level locking is a requirement driven more by the number of users, size of each row, size of pages, and the frequency and speed with which any given row will be updated.

Where users=>many, rowsize=>small, pagesize=>large, and update frequency/volume=>high, then row rather than page locking is required to keep performance high - otherwise processes get blocked waiting for a page to be released.

Where the above are not met, then page-level locking is fine - as long as your design is sound. Row level locking is not a substitute for a good design.

Why don't they strip down PostgreSQL instead ?

[f5426]

(Go on. Moderate this as flamebait. It is probably one)

I never understood why people have to re-invent the wheel every time.

Linux is slowly re-implementing what FreeBSD already have, (vm, or kqueue), and now MySQL is trying to turn itself into a real DB, althought it doesn't have the the foundations for this.

If someone (ie slashdot) have a problem with the absence of row-locking, then he shouldn't use MySQL at the first place. Maybe it is just me, but I would be more enthousiast to a 'optimise PostgreSQL' then to a 'compexify MySQL'. Probably because premature optimisation is the root of all evils.

Or maybe because the concept of a non-ACID (basic ? :-) ) database make me barf...

Cheers,

--fred

Why lock a row when you can use a transaction?

[TurboRoot]

I use PostgreSQL myself, and I never have to lock rows, its done automaticly for me by transactions. Mysql team has stated many times adding things like this requires almost a complete re-write of the database to make it work. It works well in PostgreSQL because rows are never deleted. They are appended when updated, and the old rows are still available. When no clients are connected that are using the old rows in a current transaction, it deletes the old rows the next time it finds it. Pretty simple, and it works fast. I am not another lamer saying, hay use PostgreSQL its cool. I am saying, it works. Derk

Slashdot always freezes because of one piddly post

[AFCArchvile]

It's how I know that someone's posting a reply to one of my posts: if it takes longer than 2 seconds to navigate to my users.pl, then someone's probably replying.

Of course, there's Bender: promises, promises. However, Bender still puts random spaces into long A HREF tags, so I can't refer to long URLs like Boston Globe pages. For such a good discussion site, Slashdot has some bad code issues.

Re:Row locking is already implemented - here's how

[SuiteSisterMary]

You are aware that due to the way MySQL works, while a record is being inserted/updated, no other record in that table can be touched, yes? This is a programmed behaviour. It's not that it can't do row locking so much as it DOES do full table locking, and naught else.