Slashdot Mirror
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.
These type of future announcements are unsettling. It is not really much different than Microsoft announcing products months to years in advance... It means nothing until there's something to release. Also, there's a specific danger here in the open source world... Imagine if someone were planning to add this functionality to the open source version of MySQL but didn't because of this announcement...Then imagine that the company doesn't follow through...I'm not saying we have any specific reason to distrust NuSphere...but...
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.
Vintage computer games and RPG books available. Email me if you're interested.
But you are indeed right that page-level locking can be made to work with a sound design when architected by someone that knows how to cope with this.
I'd expect it to work out best if updates are performed via a TP monitor (like Tuxedo) or a message queueing system (ala MQSeries); if updates are grouped together so they are done by a central "update process," then the number of users involved becomes less relevant.
The problem is that page locking mandates having "highly-qualified Sybase experts;" it tends to be easier to keep in stock "somewhat qualified Oracle folk," which means that the assumption of "perpetually good design" is not safe...
If you're not part of the solution, you're part of the precipitate.
MySQL is the perfect datastore for a mostly read database that needs to be fast, and doesn't need the ACID properties of a real database. If this describes your application then MySQL is probably the best solution for the job.
I find it a little disturbing that MySQL feels the need to change their database so that it becomes something that it is not. When they are done they almost certainly will be left with something slower than they have now for their current target audience, and it probably won't be as fast as PostgreSQL or Interbase for their new target audience.
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.
Ita erat quando hic adveni.
I would recommend Interbase over MySQL for just about any application. There is a very active community working on it. They forked the code because Borland/Inprise's apparent lack of real commitment to Interbase. They call it now Firebird. For more Informatin, look here.
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.
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.
This sig left unintentionally blank.
--
If you need a super-heavyweight database system, go with Oracle, or MS SQL. (The latter will never happen, no matter how good SQL Server is because /. will never admit Microsoft may have a good product)
Both are excellent database systems that support many more advanced features than just row-level locking, such as real-time English Natural Language Query systems.
Remember: You get what you pay for. Don't use a free database system then compain when it doesn't scale as well as the big boys
On a side note, IMHO the Dynamic locking on SQL Server works really well -- automatically adjusts to table, page, and row-level locking on the fly.
-----
Natural != (nontoxic || beneficial)
MySQL IS a database, and a pretty damn good one at that. However, it is not a very sophisticated database. At one time, actually not THAT long ago, MySQL level databases were state of the art. Now, of course, the state of the art has been bumped up quite substantially. MySQL is still an excellent choice for many database needs, but obviously not all of them, especially in the "high end". Not everyone needs a Lamborghini that pumps out 450 horsepower, sometimes a VW bug works just fine, that's what MySQL is.
A Press Release about vaporware due out sometime in the next six months submitted anonymously.
I can see why this is news.
Until it supports transactions & row-level locking, why is MySQL called a database?
But they can be rather useful tools. They can facilitate information hiding, and provide an object-like (keeping some behavior with the data) feel to the application. They can also make management of multiple systems and applications somewhat easier.
Let's say your company decides that the USPS just isn't good enough. The new policy is that all US customers get their shipments via UPS or FedEX. Suddenly all those "PO Box" addresses in the database are no longer valid.
So what do you do? Do you rewrite that legacy order entry application used by division X? As well as the new order entry app used by the rest of the company? (And let's not get into why division X can't use the new app, it's not under your control. Life is unfair. Other people get their way sometime, even if they're idiots. Just deal with it.) What about the new Web interface that the Consumer Technologies department has just released? Let's not forget the software that the sales department uses to give price quotes. And the Customer Service app. How many developers will that take? How long will it take to roll out after QA approves all of the changes? How many desktops, laptops, and servers are affected?
Personally, I'd just rather change one trigger or stored procedure on one or two servers. OK, so the QA people still have to test all of those different apps, but they'd have to anyway. This is still the easiest way to get it done.
Can stored procs and triggers affect performance? Yes. Especially poorly written triggers.
Can stored procs make it more difficult to migrate to another database? Yes, while making it easier to migrate the UI portion of the app to other platforms.
It's a tradeoff. Often it makes more sense to put some (all?) of the business/application logic into the database. It doesn't always make sense. That's a design decision. While designing a system, I'd rather have more tools at my disposal, and pick the best combination, rather than having my hands tied.
To email, do the obvious.
And make sure you do logical exports if possible, not just physical backups - it's a lot easier to spot and fix messed up data in a flat file.
you get what you pay for
Oh really?
"Free your mind and your ass will follow"
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.
PostGreSQL only allows *one* database per server, and doesn't have good inter-server communication. When asked about this, the PostGreSQL developer community responds that we should all just put everything in one big database, rather than splitting our databases up along functional lines and doing cross-database queries when needed. This goes against the way those of us raised on Sybase and other commercial databases were trained - and against good data modelling, imho.
That is the #1 gripe we have against PostGreSQL, and one of the main reasons we're using MySQL instead. While feature-rich, the design of the engine interferes with what we understand as good data model architecture.
The gripes against MySQL are missing features - better locking, transactions, stored procedures, and triggers. We learn to cope with them. And PostGreSQL doesn't offer stored procs at all, and requires triggers to be written in C - ugly, ugly, ugly. It ain't that much better, and in some ways it's worse.
--
Hand me that airplane glue and I'll tell you another story.
Does anybody else here find it horribly fascinating that Progress -- creators of Progress (a high-end, commercial RDBMS), WebSpeed and SonicMQ -- is expending time, effort and money in developing and marketing MySQL, a rival RDBMS?
Yup, the other way to do it is through middleware, generally objects. But at that point, your apps never touch the database anyway. And very rarely will you have seperate database backends that are running on different databases. In my experience, at least.
Vintage computer games and RPG books available. Email me if you're interested.
I believe your information here is out of date. PostgreSQL offers stored procedures using pgpl/sql and handles multiple databases per server just fine. I'm not sure about cross database queries as I haven't tried any.
If you haven't looked, PostgreSQL 7.0.2 is worth a look if the last version your worked with was 6.x.
(Go on. Moderate this as flamebait. It is probably one)
:-) ) database make me barf...
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 ?
Cheers,
--fred
1 reply beneath your current threshold.
Why not use a real RDBMS like Borland InterBase. </flamebait>
.. and yes; it is opensource.. :)
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
If you need a super-heavyweight database system, go with Oracle, or MS SQL. (The latter will never happen, no matter how good SQL Server is because /. will never admit Microsoft may have a good product)
:), but it only runs on NT/W2K, and that's a deal breaker for me.
MS SQL may or may not be a good database (it must be better than Access, right?
- Scott
------
Scott Stevenson
Scott Stevenson
Tree House Ideas
Again IMHO, transactions are a crutch. At times, they are a useful crutch, but a crutch nonetheless. There is an excellent chapter (appendix?) in the MySQL manual discussing the absence of transactions and some of the workarounds.
Row locking is a whole 'nother issue. While you can implement row-locking with atomic SQL statements, it is more efficient for the DBM to do this. Besides, locks need to go away when the connection goes away, which manual locks do not. MySQL could fix this by allowing a connection to provide a query or queries that the server should always execute when the connection is closed, so that if something disrupts the connection, manual locks can be removed...
Can we please not give notice/publicity to announcement-of-intent from companies? Show code, then collect your kudos and marketroid credits.
Unchecked, this could turn into a constant shower of press releases from hundreds of companies wanting a piece of the Linux pie. And when the majority amount to nothing, as most of these seem to, it just makes Linux look bad.
I've just started messing about with Interbase a bit and I find it to be extremely nice. I'm not a database geek by any means but Interbase provided somethings that I'm used to from my previous Oracle experience. Basically this boils down to what different people THINK a database should or should not have. MySQL leaves most things (like FK's, triggers, etc) to allow for greater speed.
"Fighting the underpants gnomes since 1998!" "Bruce Schneier knows the state of schroedinger's cat"
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
Most ascii file schemes end up eating some shit usually. And Access
--
Why do people have this need to criticize it?
/. 'groupthink' is 'sucks to MySQL' .. assholes. Many people (myself included) like it for its simplicity and outright SPEED. If you want to have some dynamic content on a site - I'm not talking a full-blown e-commerce site, just some small stuff like .. OH, a discussion forum perhaps?? - then MySQL is great for the task.
I have no idea and it bugs me too that the
If you don't want to use it, noone is forcing you people.
--
Delphis
Wait! I wasn't ready.
Dang, I'm always missing out.
Pete
> Here's my guesswork:
Here is mine:
MySQL have been designed to be lightweight and fast for non-critical applications.
Users of MySQL have happilly fit this definition, but now, more and more of them are going higher profile. Instead of migrating to a more adequate tool, they want to migrate the tool itself. IMHO, a bad idea.
Locking is definitely not a trivial subject. I doubt that it can/should be implemented as an afterthought.
Cheers,
--fred
1 reply beneath your current threshold.
Is because Borland has open-sourced parts of the Interbase engine...
Speaking of which, has anyone test-driven the open-source version of Interbase (In either the Windows or the Linux)? How good is the source code?
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.
"Ancillary does not mean you get to rule the world." --U.S. Circuit Judge Harry Edwards, speaking to the FCC's lawyer
Two year per mhz licenses are pretty damned cheap especially when considered against ms-sql. With ms-sql you'd have to upgrade every two years anyways as MS does not support older software. OTOH why pay at all? Postgres, interbase, and sapdb are all open sourced.
War is necrophilia.
It's not a relational database unless it enforces referential integrity. MySql is not a relational database.
War is necrophilia.
Yeah, right. tell that to MS. I think that we can be clear that what you (don't) pay for a product is not necessarily related to the value/power of the product.
You don't get what you pay for, you get what you choose, then you pay for your choice.
mySQL is not (currently) being touted as a real alternative to Oracle for large databases. It has it's uses, and that's not one of them.
`ø,,ø`ø,,ø!
Free Software: Like love, it grows best when given away.
Why lock AT ALL when you can use InterBase?
/ index.htm
Interbase doesn't lock anything, it simply makes a new version of the row that becomes the current when the transaction is comitted.
MySQL is, IMHO, a piece of crud compared to ANY real DBMS, as it doesn't have any of the features that are needed in a database (like foreign key constraints or transactions)
That said, if you don't compare MySQL to flat files it does come out on top, it's pretty neat as a structured storage, but it sucks so badly when you have more than one table that needs to be in a consistent state.
InterBase is *VERY* nice and pretty fast, but it has rusted quite a bit from v4 to v6 and it's DBD was completely useless until quite resently, after lots of hacking it rarely deadlocks now, but it (IB) is quickly on it's way out of our setup.
For a REAL database that scales the pants off anything that is Free software today (it can support SAP R/3 with it's 16000 tables) check out SAPDB:
http://www.sap-ag.de/solutions/technology/sapdb
I's set to come out under GPL(!) in April next year, but I've been using it for a while now and it seems to be a *really* nice DBMS.
-- To dream a dream is grand, but to live it is divine. -- Leto ][
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.
Vintage computer games and RPG books available. Email me if you're interested.
Vintage computer games and RPG books available. Email me if you're interested.
Vintage computer games and RPG books available. Email me if you're interested.
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
I can deal with slashdot repeating the same story a few times, but when CmdrTaco repeatedly abuses the English language, it's more than just a little annoying. :-P
> 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.
-- MartinG To mail me: echo kewyjlcxyzvjfxbqwh | tr bcefhjklqvwxyz
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.
All the best,
--Bob
It's a application built on top of a database. and 1600000 tables is hardly a fair comparison. You can have 10,000 tables in mysql and not have a problem.
SAP runs great w/MS SQL Server or Oracle.
--------------------
some low level rocking.
"First rule of government spending: Why build one when you can build two at twice the price?" -- Mr. Haddon, Contact
Anyone got any experience with both they'd like to share?
How do they compare with MySQL w/ Berkeley DB tables?