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.
Nonsense. Just spin on j until i gets paged and the ReiserFS raises an signal that's logged to syslogd. Then parse the syslog for lost data and reclaim it on stick em notes. Should be no slower than a Java implementation. Really, this isnt rocket science, you know.
Btw, Bababooey to you!
You could use another DB...just a thought.
didn't borland open up their db?
JediLuke
JediLuke
-Do or Do Not, There is no Try
Now THIS is funny. Somebody moderate this up -)
I don't know about anyone else but I'm tired of people dogging on mysql and the things it doesn't have and things it "should". If you use it you will find it has strong areas and weak ones for most applications you will find the strengths outweigh the weaknesses. That is of course unless you are doing multitiered POS/Banking/Finance type integration the first being the most common where it might not be a good idea to use. Another argument is that you have to have transactions to e-commerce. Which I don't feel is true there are hundreds of ways, some possbly easier than implimenting transactions, to deal with the fact mysql doesn't have them.
The impression I get is there are DBAs out there who don't know anything else but the DB they were raised on let alone how to learn a new one. And there are people out there that believe anythign their highly paid DBA tells them. This is just the only way I'm able to rationalize anyone would suggest mom and pop PC retailer on the net should pay 3-15k on a database that may never see more than a couple thousand records. Esp when there are alternatives that are in some cases free that give them the functionality they need without starting them off in the red.
I think the quasi proffesionals who fail to realize the importance of mysql in the industry aren't real proffesionals at all.
P.S. didn't mean to not include any of the other free databases out there but I could not call myself an expert in them yet so I cannot argue there strengths as well as I could mysql/oracle/mssql.
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...
Why does it matter? Isn't that what schemas are for? Every connection to Postgres gets its own process, so what does it matter if rows are in the same database or not? Maybe the fact that you were splitting up your databases are because of limitations in Sybase, and not because of the inherent goodness of splitting. Please clarify your post, or clarify your understanding of PostgreSQL.
Engineering and the Ultimate
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.
Um, not it's not. It's called a spreadsheet application. When people say "an Excel spreadsheet", they mean a spreadsheet created with Excel. Likewise, when somebody says "an Oracle database", they don't mean the application, they mean the database which Oracle is managing.
Sometimes, a tool and its function ARE the same thing (Hand me that level, I don't think this table is level). The tool that is used for leveling tables is, indeed, called a level... but Oracle, PostgreSQL, and others are not called databases, they are called database management systems.
Information wants to be anthropomorphized.
I just feel that it is possible to logically isolate the business logic (design ) while physically deploying it with the data store (implentation).
And yes, the reasons to do that include time-to-market. (Valid reasons for doing so do not include cost. Two cheap application servers and one mid-range database server will often outperform one high-range database/app server, depending on where the particular application's bottleneck is.) And yes, there is a tradeoff. That was the point that I was trying to make, even if I did not express it as well as I would have liked. It is a tradeoff, and as long as you get what you want (TTM) and you know what you're paying (scalability and portability) then it's acceptable.
To email, do the obvious.
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.
InterBase actually pioneered the algorithm, which they call "multi-generational". Here's from an InterBase marketing blurb:
- Multi-Generational Architecture and Row-Level Locking
So InterBase deals with conflicts through row-level locking. So it does not deal explicitly with "dependencies", but rather the first transaction to commit succeeds; later transactions will fail because the versions timestamps will mismatch.InterBase's unique Multi-Generational Architecture (MGA, also known as versioning) enables clients to access an InterBase server with high concurrency and throughput. Also, the manner in which InterBase stores multiple versions of a given data record means that each client sees a consistent snapshot of the database. A client can update data without interfering with other clients' views of the data. These features result in DBMS technology that is well-suited to handle the short-lived transactions common in OLTP, concurrently with the longer-lived transactions of OLAP.
The server implements true row-level locks, using an optimistic locking strategy. Any number of clients can share read access on a record simultaneously; contention occurs only when two clients attempt to update the same record. In that case, the client who was first issuing an update operation has the option to commit the operation, and the server returns an error to the other client(s)
The combination of versioning and row-level locks give InterBase exceptional throughput and concurrency, when compared with RDBMS implementations that use page-locks or exclusive locks for reading. Readers never block writers, and writers never block readers.
InterBase handles all versioning and locking transparently and automatically for the application. This relieves the developer from the manual locking control required in some other RDBMS products. A number of optional parameters for transactions permit developers to specify other locking behavior.
A more technical summary can be found here
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 should go away now... hasn't PostgreSQL surpassed it in performance (by recent benchmarks)? Data consistency should not be an issue with any db, no matter how piddling the content seems to be!
or M$ $QL $erver
Unfortunately, when you choose MySQL, you're locked in. But you could still port slash to PostgreSQL with some clever scripting. But why would you want to? Slash includes workarounds for MySQL's shortcomings that real RDBMS's don't have.
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.
Lol. Thanks. Last sentence make the troll a bit obvious, but the rest is darn funny.
Cheers,
--fred
1 reply beneath your current threshold.
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.
Postgres is not GPL'd - it's a BSD license, which makes it far friendlier to embedded systems developers than MySQL (eg: Cobalt RAQs).
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.
Well, the funny thing is that they have
pre-announced these features, and say they
will achieve them by using Berkeley DB as
a sort of wrapper. I believe there's a
version in beta now.
>Replication, transactions, row-level locking,
> stored procedures and triggers are NOT
> requirements of a database.
That's a red herring - the whole point of the
discussion is MySQL's claim to be a _relational_
database, which it isn't.
> A few good reasons not to use triggers and
> stored procedures are:
>
> 1) Performance
> 2) Portability
Them ain't a few, them's a couple, and half
of them are wrong. When you use triggers and
stored procedures, you aren't interested in
portability. If portability is an overriding
concern, you'd use ODBC and never use the
server's native API at all. Of course, you'd
do that at the expense of performance, which
brings up the (dead wrong) second point.
Performance is exactly why you use triggers
and stored procedures. When used appropriately,
there isn't any way to gain the performance
enhancements they offer without using them,
unless you like the idea of making all of your
clients also behave as multi-threaded servers.
It doesn't make a bit of difference where the
code is stored, it's where it is executed that
makes a difference in performance. If you
don't grok that, you're missing one of the
main benefits of client/server architecture
versus client file-sharing.
Your column flag work-around for row-level
locking provides a textbook example of how to
slow a database to a crawl. You need to read
a good book on concurrency and deadlocks to
know why you're scheme is disaster bound, e.g.
(one of many) what happens when one of your
clients crash after setting and before
resetting your flag?
--
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)
Triggers and stored procedures provide increased performance in specific situations. Thses usually involve very few client conections (not really enterprise class apps). Replication and mirroring aside (which you would only need for back up and fault tolerance, if you designed right) there should be only *1* data store, however, there are multiple clients. By concentrating your logic in the data store you are creating a bottle-neck instead of distributing the load across clients. This is why triggers and stored procedures are generally bad (for large apps). If you can't understand this, then there is nothing that can be done for you. See above discussion. Distributing the load across *multiple* clients as opposed to a *single* data store REDUCES lag. As far as a client dying unexpectedly, see the original post, use a timestamp for the flag and use a timeout. If the lock's expired then tough shit for whomever set it.
PostgreSQL _IS_ a lot faster for some things, especially complicated selects. In addition, it has SERIALIZABLE transaction isolation support (email me if you don't understand how serializable differs from other transaction isolations), capable of views(why have a database without views?), allows you to create functions in PGSQL, C, or a few other languages, supports triggers and other wonderful things. For basic stuff, its not much slower than MySQL, and for complicated things, it is much faster. In all things, it is much more flexible.
Engineering and the Ultimate
I am not sure if C is the problem.
Locking in a database is not implemented by 'locking pointers'. The 'pointer' address can be
used to generate a lock object.
(Notice: I am a fan of MySQL and use it myself)
Even though I'm not familiar with Slashcode, i'm sure it would not be hard to transfer to another database package because of the Perl DBI 's generic database interface. As far as I know, you would just have to change up the connection code if Slashcode were written well.
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.
MySQL is gaining popularity because it is so fast. The reason it is so fast is because it supports almost none of the features that people want in a RDBMS. It really is only useful for small datasets. Now people are trying to use it for larger datasets and more complex things, and finding out that they really do need those extras that normally come with RDBMSs. Once those things finally get implemented in MySQL, it will be just as slow as any other database. So, for large datasets where manageability is an issue - go for PostgreSQL - MySQL will make it there eventually, but Postgres is there now.
Engineering and the Ultimate
According to their documentation, the latest version supports transactions if you use Berkeley DB tables, albeit they still consider it to be alpha. See http://www.mysql.com/documentation/mysql/commented /manual.php?section=BDB
For complex queries, Access's query engine / optimizer is actually VERY good. For complex queries, MySQL does a poor job; it can't do a lot of types of query feature, and does not optimize well.
Of course, I have built web services on MySQL, not on Access.
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.
The title pretty much says it all. I am curious on why this hasn't been added before. I am not an expert on databases, but have worked with a few extensively, and it seems to me that this should be something 'standard' with any DB that is expected to handle non-trival amounts of data.
Any ideas on why it took so long to add this?
Eric Gearman
--
Atomic batteries to power! Turbines to speed!
niceFire.com - Humor and Lego's or Lego's and Humor or Some Combination of
Yeah, what's holding up our semi-daily :Cue:Cat story? It's almost lunchtime on a Monday morning and we haven't had a chance to mock Digital Convergence yet.
hey, check it out, man, meeptool featured the Battlin' Siezure Robots! what next, goatse.cx?!!! heh heh heh.
It is important to note that mysql is currently one of the fastest db servers on the planet. Sure it lacks some features, but stuff like load balancing comes with time.
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.
Databases have 2 simple requirements:
1) Reliably store data
2) Reliably retrieve data
Replication, transactions, row-level locking, stored procedures and triggers are NOT requirements of a database.
However, MySQL *DOES* have full transaction support through the use of Berkley DB tables.
A few good reasons not to use triggers and stored procedures are:
1) Performance
2) Portability
Application code should be kept in the application, not the data store (which is what a database is). Those of you who feel that MySQL is not a real DB do not know what a real DB is. Your views are tainted by Oracle marketing.
As far as row level locking goes; It can be easily implemented throught the use of a flag column in your tables. This can be used for passive or active locks. This flag could be:
1) userID
2) incremented integer
3) time-out (DATETIME)
4) userID+time-out
Consider reading a products [current] documentation before you say what it can and cannot do.
-- (my apologies for this semi-rant and poor speling) --
Of course, it is customary to bash M$ here. However SQL2000 is enterprise quality. Right now, if you are not running Oracle, MSSQL or DB2 you are not in the big leagues. Unless of course, you are running a proprietary solution.
It can store and retrieve data pretty reliably.
Hey, MSSQL6.5 doesn't support row-level locking (well, it does insert-row-level locking, but i don't think it supports it on updates, for example)
I don't think you understand what the purpose of locking is. Sometimes it may be desirable to lock at page level. Other times at table level. Other times at row level.
transactions can let the dbms decide, but the dbms doesn't always pick the right locking mechanism for your application. How long do you hold the lock? On an insert, do you want a whole page locked, or just a single row?
Row level locking can speed up concurency. Some DBMSs will escalate the locks for you, and some do a better job than others. But to say you never have a reason to lock, it's simply silly. Try writing a 200+ client application and not worying about locking.
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 this mean that the Slash gurus will come out with Slash 1.0.10 to allow Slash to work with the latest version of MySQL?
It's either on the beat or off the beat, it's that easy.
I moderate therefore I rule!
--
Pretty silly. You have locking problems deleting from your locks table. :) It is also very expensive. We use up about 70,000-100,000 row locks in a current schema. If the application implemented locks in a table, it would slow to a crawl.
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?
You can't run MSSQL in a colo facility. Remote access is of the "not enough, too slow" variety. Sorry, the Russian Hacker variety doesn't count. I know 'cause a friend whose company got bought out ended up having to make a colo decision (they wanted his servers in their colo), and rather than do that with NT+IIS+ASP+SQL Server, he migrated to RedHat+Apache+Perl+MySQL, and has an order of magnitude improved performance.
Dog is my co-pilot.
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.
Row locking in Oracle is automatic. A modified row in Oracle is always locked exclusively until the transaction holding the lock is committed or rolled back.
Why not use a real RDBMS like Borland InterBase. </flamebait>
.. and yes; it is opensource.. :)
But you get what you pay for, dont you. And we all know how unreliable and unstable apache is, right? You had better go and shell out some cash to Netscape or somebody else if you want a reliable webserver. Please!
Just because Mr Ellison wants to drink your blood after he has raped your bank account, does not mean that his product is infallible. Having said that, anybody who knows their db's would have to agree though that Oracle does do a rather nice job.
But I will support MySQL till the day it bombs out and is not recoverable (this has not happened to me in 5 years). I will do it simply to say fuck you to Larry and simply because I cannot afford to buy him a new tennis court and simply because if we all support MySQL now, one day when transactions, stored procedures and row-level locking are implemented we can all take the money we saved by not buying oracle and have a big party and not invite the fools who still think that if you pay peanuts you get monkeys.
Lets all say it together: O-P-E-N--S-O-U-R-C-E.
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
Why not use Postgres? It's a real database, real locking etc., commits/rollbacks (called "Transactions" to you msql/mysql boys) and it doesn't have a communist licence.
.iMMersE
OK, I concede it doesn't have "real locking", but check out the MySQL site and you shall see that "commits/rollbacks (called 'Transactions' to us msql/mysql boys) are there, and the communist license is actually the GPL.
codegolf.com - smaller *is* better.
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...
Unfortunately, Oracle is priced such that a mere pony won't suffice. They want most of the horse in the stable.
er, I should say "*the pages with* database connections :-)"
* And remember, it's spelled N-e-t-s-c-a-p-e, but it's pronounced "Mozilla."
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"
Remember Delphi? Remember Kylix? What planet do you live on?
---
GetSystemMetrics(SM_SECURE) == FALSE
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
..or Delphi.
---
GetSystemMetrics(SM_SECURE) == FALSE
Is Postgres the only DB that will run on Linux and does rollbacks? There must be others, I would imagine journaling is a must have for serious work.
It's not really of concern, soon they may stop, as probably 70% of slashdot readers block doubleclick one way or another anyways (I do).
---
GetSystemMetrics(SM_SECURE) == FALSE
Kudos to you for writing or own, and kudos for using PHP, the Web Programming Language of the Gods (akin to Object Pascal, The Programming Language of the Gods).
---
GetSystemMetrics(SM_SECURE) == FALSE
What do you mean, MySQL has no staff to speak of?
We do - I am part of it, and this is my full-time job. We spend quite a bit of
time answering questions and fixing up systems of
our customers in addition to developement.
MySQL Developer
It's incredible how people buy the marketroid BS about rdbms systems.
... will not see "some data" but rather "some old data".
What are transactions anyway?
It simply means that when you store:
(85, "some other data")
it gets stored:
(23, 85, "some old data")
(32, 85, "some data")
With 23 and 32 the transactions that have created the data.
After a while, transaction 23 gets sweeped (=deleted) by some sweeping process.
In the meanwhile, as long as transaction 32 has not been committed, all the other transactions 23,24,25,..., 33,34,
SELECT COL1,COL2 FROM T1
conceivably becomes (optimized rather than in bland SQL)
SELECT COL1,COL2
FROM T1, TRANSACTIONS
WHERE T1.TRANSID=TRANSACTIONS.TRANSID
AND TRANSID >=
(SELECT MAX(TRANSID) WHERE COMMITTED=TRUE)
Of course, in a pointer arithmetic oriented system language like C, this stuff would go very fast, but rather hard to implement too.
Unfortunately, we need this speed rather badly, on the relatively slowish hardware of today.
Row-locking is very similar, with some in memory table getting consulted before touching a particular row (can you smell the overhead?) and transparantly having a column in every table that uniquely numbers the rows.
MySql blazes, faaaast, because it does not implement the overhead for this. If you implement the table transaction, and the table rowlocks (preferably somewhere in-memory), you've got transactions and row locks implemented anyway.
If they build it into MySql, you cannot get rid of the overhead any longer, if you don't need it.
I guess the solution could consist in turning Mysql into some db framework with adaptor classes, mostly written in something higher level (like compiled GCJ Java), and then supply your own adaptor classes that implement the additional features that you need for a particular project. Note, however, that MySql wouldn't be as fast any longer, and therefore lose much of its appeal in the process.
Oracle certainly is awsome though when you are a mulimillion dollar corporation with vast amounts of data. Oracle and MySQL can't really be compared because they really serve different markets.
http://www.postgresql.org/
And, yes, this is flaimbait.
Deleted
I believe that's all called 'Inprise' now, instead of Borland.
--
Delphis
I couldn't have said it better myself. Thank you bazxissimo.
Keeping
I've worked with Oracle extensively; it's nearly failsafe, and handles inserts well, but it's slow as balls. Good for sensitive transactions on low-traffic sites, but if you want real RBDMS power with speed, only PostgreSQL offers it.
That is, unless you're just dying for the cool CTX stuff that Oracle offers. 'cause that just rocks.
My junkbuster proxy has been blocking more and more of slashdot ads lately. And I haven't changed its filter rules. So to me, this is a sign that slashdot is moving more and more to "mainstream" web-ads just like any other free website. Good or bad? You decide. Not a problem here, junkbuster prunes them all out anyway.
I want to delete my account but Slashdot doesn't allow it.
Watch what you say, sxpert, M$ may sue you for breach of EULA for mentioning those benchmarks. Oracle found that one out the hard way.
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
How many times do you think you will find the code:
...
EnterCriticalSection(cs);
... dosomeupdate
LeaveCriticalSection(cs);
in any database source code?
I guarantee that it's all over! and it does nothing more or less than stopping all other threads! So everybody shuts up while this particular guy is speaking. When it's a process per user instead of a thread per user, you see mutexes acquired all over the place.
For any practical purpose a single writing table-touch practically always means a table-lock.
Okay, freepascal.
---
GetSystemMetrics(SM_SECURE) == FALSE
How do you "work around" lack of transactional support (as quoted from the original post) aside from just not using transactions? When multiple updates must all complete in order to maintain data integrity and your code craps out after the first x, what mechanism is going to prevent the subsequent mess? Back in the late days of dbase there was code that tried to mimic transaction support using exception handlers and such, but if your code flat-out craps completely out, you are screwed.
Maru
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.
How you can work around no transaction support? That's BS and you know it. There is no real workaround.
---
GetSystemMetrics(SM_SECURE) == FALSE
No it's not. It's stupid IRC shit. Go away, stupid IRC shit.
---
GetSystemMetrics(SM_SECURE) == FALSE
Realtime, distributed indexing of the Internet. Coming soon!
Kord
Of course, MySQL has NO staff to speak of (other than the development team), but still, you get what you pay for.
"Ancillary does not mean you get to rule the world." --U.S. Circuit Judge Harry Edwards, speaking to the FCC's lawyer
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
Find something decent to report on, like the ATI Radeon problem with the capacitors breaking off.
"Ancillary does not mean you get to rule the world." --U.S. Circuit Judge Harry Edwards, speaking to the FCC's lawyer
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.
--------------------
Hey there, muppet, I just went to tpc.org and noticed something funny: they don't benchmark MySQL at all! In fact, they don't do PostgreSQL either. Your benchmark site looks like a pay-db only club.
some low level rocking.
"First rule of government spending: Why build one when you can build two at twice the price?" -- Mr. Haddon, Contact
I would, but I can't afford a 1500 UPU perpetual license for Oracle (2x500Mhz RISC = 1500 UPU = US$ 22.500,-) *sigh* I need another alternative. Not mySQL, not Oracle, something else... What?
Any technology distinguishable from magic, is insufficiently advanced.
How the hell do you port that to another DB program easily, think about the sheer size of slashdot, or any other website stuck on one type of DB. That's why he's excited and not changing. Myself, I'm working on making a dynamic website and im using mysql right too, because its there, no other real reason.
So for all of you who want slashdot to use a _real_ DB, download slashcode and change it, its there, I bet Rob (and I) have bigger problems to worry about.
No I don't know him personally, but neither do you
Lemure, wtf! Don't you mean Lemur?
Anyone got any experience with both they'd like to share?
How do they compare with MySQL w/ Berkeley DB tables?
It's an open source project -- if someone wants to do the work to turn it into a more serious product do you have a problem with that?
So PostgreSQL and InterBase are basically worthless as well, I take it?
-- It only takes 20 minutes for a liberal to become a conservative thanks to our new outpatient surgical procedure!
...when your whole DB freezes because of one piddley write, its more then just a little annoying.
When Slashdot makes elementary grammar mistakes like these, it's more than just a little annoying.