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:Why lock a row when you can use a transaction?

[Earlybird]

Oracle, InterBase and PostgreSQL all implement a generational table algorithm. This is why they do not need transaction logs. Sybase databases and Microsoft SQL Server don't, and rely on icky transaction logs (journaling).

Both schemes work well, but the former is potentially must faster because there is no lock contention: Nobody can ever be writing to the same piece of data, and nobody can ever be reading something that being written, unless explicitly specified in the isolation level.

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.

Re:Enough already

[DrWiggy]

There's no pleasing some people. Whenever a story in the past has come up about MySQL, the PostgreSQL kiddies run around like headless chickens, screaming about the lack of integrity and row-level locking, lack of transactional support, etc.

Then, a story is posted saying how MySQL will address these issues in the next 6 months, and all of a sudden the same people start running around screaming at us to shut up, and why bother in the first place, because putting such functionality in is a stupid idea anyway.

Plain and simple, MySQL is faster and has multi-user (at the DBA level) support. It means different applications only get access to the SQL tables they need. It means you can give users their own databases easily. It's incredibly fast. All it lacks is a little transactional auditability and data integrity - for most applications run out here on the net, this isn't an issue, and if it is, you can normally work your way around it.

Now, MySQL is going to get the features that will make it fit snugly into the low-end RDBMS range of open source DBs, and we're going to be happy, because it means our data is going to be safe. We're even prepared to happily take a hefty performance hit if need be.

Unfortunately though, people seem to take this matter as seriosuly as their religion, and we end up with this sort of argument. This is all seeded from either jealousy, bigotry, or both. The argument as to which DB is best is one for you to decide all by yourself.

However, when I saw this story, a smile appeared on my face, because it's the one thing, and the only thing, that I actually wanted MySQL to incorporate at some point. Sure, the model and philosophy sounds a bit kludgey but I don't care because as long as it works, I'll be happy. :-)

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.

wow

[kaisyain]

A Press Release about vaporware due out sometime in the next six months submitted anonymously.

I can see why this is news.

Why wait for the next version?

[brianvan]

Just pony up the dough for Oracle in the first place, ya cheap fux0rs.

Or, if you need something quick and dirty, go with the quickest and dirtiest of them all: Access. (that is, if you have a Windows box available)

I'm being serious. MySQL has it's good uses, but if there's severe limitations in its use, why wait for it to get better? (if it will at all) I don't believe that the announcement is vaporware, but there's more problems with MySQL for serious usage than row-level locking. If you're not happy with it right now, I can point you to a handful of solutions that will fit your needs one way or another.

And if you are happy with it right now... well, then nevermind, have a nice day.

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

Row locking is already implemented - here's how:

[Anonymous Coward]

As a mather of fact, it's very easy to implement row-level locking using any database engine that support unique-key indexes.

To do that, just create a new table called "locks" with 2 fields:

resource_name: char[80]
user_name: char[20]

and create a unique index on resource_name.

In order to perform a lock on a specific resource, like a table row, you just have to do this:

insert into locks values( "TABLENAME:ROWID", "USERID" );

Where TABLENAME is the name of the table and ROWNUNBER is the number/ID of the row you wish to lock.

If this resource wasn't locked yet, then a new row will be inserted in the locks table.
However, if this resource was already locked, then it would create a duplicate entry in the resource_name column that would violate the unique index.
This way, the unique index would stop INSERT from inserting this line into the locks table.

You can create a very simple lock( table, rowid ) using this insert, that would return true if INSERT worked OK and false when INSERT fails.

To unlock a resource, you just have to remove the respective row from the locks table.

delete from locks where resource_name="TABLE:ROWID";

The user_name field can be used to clean all locks that were active when a user connection drops.

delete from locks WHERE user_name="USER";

This works becuase insert will check for duplicates in unique indexes in an atomic operation.

You simply cannot have two persons inserting the same lock in the locks table, because insert is ATOMIC.

fjp_at_linuxstart.com

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.

Re:Sounds like it is about time

[Skater]

Well, if you read the MySQL home page, they clearly state that they created a database for their own use. Apparently, they didn't need the feature.

Not everyone needs a total heavyweight database system. Since I'm the only one accessing my databases, I didn't even realize it didn't do row-level locking.

Everyone is so harsh toward people who write a product and release it for free...I'm just glad these tools are available because it allows me to experiment and learn without spending thousands of dollars for hardware and software.

--RJ

Re:Sounds like it is about time

[MartinG]

> Any ideas on why it took so long to add this?

Here's my guesswork:

Probably until recently, the average MySQL user hasn't taken it seriously enough to rely on it for large amounts of data, so it was unneccesary or perhaps even undesirable - row level locking is _slower_ to a point for small data sets.

More recently though, I expect more people are starting to use MySQL "seriously" as it matures, storing much more data and suddenly the need for row locking shows itself more clearly.

It will still be slower for very small data sets, but thats a small price to pay for the scalability benefits row locking brings.

fractured IP addressing scheme

[Bob Abooey]

The real problem lies within the addressing scheme that the C language uses. As we all know MySQL is written in C, which is the proper language. However C doesn't really have arrat's they are laid out in a linear manner in the heap of your program. So we need to be able to do pointer arithmaic when we access a row in the database. Yes, this is ultra fast, but the dangers are great. Say for example I have a mutex on J in the following schema: data [j][k], then it is vital for me to lock that row with a simple ((j * data) * k * sizeof(data)). Pretty simple no, but the problem is that you cannot lock a pointer. I think they will have to re-write MySQL in java to fix the problem.