Posted by
Hemos
on from the talking-wtih-database-lords dept.
dlc writes, "Zend has an article about Michael "Monty" Widenius, of MySQL fame, which focuses mainly on the evolution of MySQL and its relationship with PHP. "
Sorry, but Sybase implemented transactions and sp's before MS bought the code base at around version 5 of Sybase SQL Server. The two projects forked at that point.
Microsoft used to supply Sybase with the DOS version of isql.
Sybase ASE is still IMHO a superior product to MS SQL Server.
Even more significant, you might be doing operations outside of the database (writing a file, sending an email) that if it fails, you need to roll back your DB transaction.
Transactions are essential. Anyone who thinks otherwise has never built a robust, scaleable system. Look at slashdot and you'll see what I mean:)
"As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing."
Oh, so you can roll back these atomic operations without having to write additional SQL to do deletes? And you can wait until all your inserts/updates are done before committing the changes to the RDBMS?
Why the world needs transactions: if you have an entity that spans, say, 3 tables, you're going to need 3 different SQL INSERT commands. If one of these commands fails, you need to back out the others. For instance, lets say you successfully inserted into 2 of the 3 tables. Your third insert fails. Without transactions, you have to manually go back and issue potentially expensive deletes. With transactions, you simply issue 'rollback' and poof, your data is back to normal.
-- ---
[[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
selector:@selector(disableUserAccount:) na
Re:What's so great about transactions?
by
caffeine
·
· Score: 2
Like i said before, if you have an entity that spans 2+ tables, you're going to need transactions. If you use triggers, you're going to need transactions. If you use foreign keys, you're going to need transactions.
A transaction treats any number of SQL statements essentially as one -- if one fails, they all fail. If they all succeed, you can commit the transaction and have the data written out to the tables. Transactions are heavily used in conjunction with foreign keys, triggers, et al to support DATA INTEGRITY. Sure, MySQL may be quite fast, but if I have to reinvent the wheel of foreign keys and triggers, and the backend DB doesnt help me maintain data integrity, why would I care about how fast my queries are on completely irrelevant and stale data?
For example ( w/o a trigger ): ---[ CUT ]--- BEGIN TRANSACTION; select nextval('trans_seq'); -- store this in a variable in your code
update account set balance=balance+45.00 where account_id=1; COMMIT; ---[ CUT ]---
Lets say the second statement succeeded and the third ( update account ) failed. With transactions, you could NOT commit the data. Without transactions, your database is now shoring erroneous data; the account's balance is incorrect. Your query to pick up the account may be blindingly fast, but your data is erroneous -- and your company just lost $45.00 (in this transaction alone). Now apply this to an e-commerce site, where customers may order well over $1,000 at a time. Would you want to potentially lose $1,000 just so you can have your queries come back real fast, or would you want to sacrifice some speed in order to have your db essentially maintain itself? Bear in mind that I've had numerous queries under PostgreSQL come back much, much faster than they did under MySQL with the SAME DATASET. And PostgreSQL has transactions, views, foreign key support, triggers, etc.
-- ---
[[NSNotificationCenter defaultCenter] addObserver:systemAdministrator
selector:@selector(disableUserAccount:) na
What's so great about transactions?
by
daviddennis
·
· Score: 2
I hear this all the time: "mySQL isn't a real database because it doesn't have transactions."
insert into line_items (order_id,....) values (order_id) [repeat as necessary]
update changes set done = 'y' where id = order_id
Ignore / retry from logs any transactions where done = 'n'.
Isn't that all transactions amount to, at least for something that isn't a full-blown double-entry accounting system?
When I first started using Unix SQL databases, frankly mySQL was the only one I could afford. (I actually started using mSQL and converted to mySQL).
Now I work for a mid-sized company, and if we really wanted to we could probably afford Oracle. But it sounds to me that you can only get the performance out of Oracle you need by hiring an extremely expensive Oracle professional to run it. And, of course, I'd have to ascend a fairly high learning curve and rewrite all my scripts, since the API bears not even the slightest resemblance to mySQL's.
Since everyone wants these things done yesterday, mySQL was the only viable solution for my situation. And so far, it's worked perfectly, even with heavy load testing.
But I can't help but be concerned at all the people who say they're running into trouble with it at high volumes. These reports are a little vague for my taste; does anyone have real-life war stories?
D
----
Re:What's so great about transactions?
by
daviddennis
·
· Score: 2
This is why I don't deal directly with financial information on my web site order database - orders eventually get sent over to an accouting package (running, sadly, under MS SQL Server) which does the heavy lifting of updating balances and such. I bring them in as sales orders, and when they are complete in the accounting package they are turned into invoices, with the implications this has for the customer balances.
Since I'm not maintaining a balance (where I recognize transactions are essential), I should be OK, unless there's something here I've missed.
Thanks, but I RTF website. I did not RTF todo list, as someone else has pointed out. I understand the speed vs. transactions tradeoff and agree that sometimes an application requires one more than the other. If you would RMF posting, you would see I was simply pointing out that, IMHO and in many other people's HO, transactions are an essential part of any SQL server, let alone the best SQL server.
The main target is to make the best SQL server there is.
But MySQL has no transactions. Many folks wouldn't even classify MySQL as a database system. Now I know he said "SQL server", not "database system", but the two are almost synonymous.
I wish the interviewer had followed up on this point. I like the speed of MySQL, but I can't take it too seriously due to the lack of transactions.
PostgresSQL is open source and supports transactions and stored procedures (in many languages).
Interbase is open source and supports transactions and stored procedures.
They also both support versioning to get away from lock problems in high transaction environments.
OF course sybase, oracle, informix, db/2 also support transactions and stored procedures. Right now ms-sql has about 10% of the database market and they are being squeezed from the top by oracle and IBM and from the bottom by postgres, interbase and sqlanywhere. There really is no compelling need to use sql server unless you are married to NT or Microsoft. You could save a TON of money by going open source.
I don't know what your needs are but have you checked out postgres. It's great and very easy to use. I use pgaccess as a front end to it and prefer it greatly to the SQL enterprise manager (which IMHO is a piece of junk). Lately I have been playing around with interbase some too and I am very impressed with that too. Capable, easy to use, stable, and free can't beat that with a stick.
Perhaps I'm "married to NT" (or rather, have customers that are)
After doing a bit of database heavy development in ms-sql 7 and Oracle 8 the former actually wins big time. (and I was very prejudiced against ms-sql before)
ms-sql has flaws naturally, but they are mostly flaws in NT, not in the database server.
When we ported some functionality from ms-sql to oracle, instead of setting the permissions in one place (user NN has the right to excute these procedures and nothing else, everything else is reserved for the admin) we had to grant NN table rights in seven different places. Guess which method is more likely to leave security holes?
I've said it before and I'll say it again: Microsofts back office products are great When your time costs more than the licence fees
Windows and office etc is a completely different matter though. They are rather cheap (unless compared to free(beer) products) but end up costing you a ton of money in maintenance.
Please don't flame me but I've fallen in love with MS SQL Server after using it in a coupla projects.
Why? Mostly because the transactions and the stored procedures.
For one web project we had a real SQL guru writing procedures for every "legal" action. Life was *so* easy for those of us doing the web coding and *so* hard for anybody trying to abuse the system.
For once M$ has added useful features instead of talking paperclips.
[First, my credentials: I'm an Oracle DBA by day and a MySQL developer on the side.]
In my experience, lack of transactions isn't a big deal in many cases, particularly for web-based applications. Much of the web is built around "stateless" sessions, so it's a good idea to open and close your database activities every time anyway. You can't very well leave an insert/update transaction hanging open while you wait for some web site visitor to hopefully click that "submit" button.
The other factor is that database updates happening on the web are user-oriented. This tends to decrease the likelihood of two people trying to update the same data. While I'm typing this, there are a thousand other people working on Slashdot postings at the same time, but the chances of us trying to update the same database record at the same time are, I would imagine, pretty slim.
There's no such thing as "rollback" on the web. In the cases where you need that kind of functionality, you can build it yourself without extreme effort.
Agreed. Which is why I pointed out that maybe MySQL is the "best SQL server" for people with more sense than money.
Best example I have -- I am currently coding an information web site for a small non-profit. 98% of the site is dynamically created (a la Slashdot), and the remaining 2% will require some heavy code in PHP to create the transaction type logic I've described elsewhere in this thread.
Now then, this info site might remain an unknown spot on the web, or it could suddenly get very very active like/. did about 18 months ago. Where suddenly I need all the speed I can get out of the database.
Now then, since I don't know how heavy the load will be ahead of time: do I ask the non-profit to pay the heavy, heavy license cost and support fees for Oracle, SQL Server, DB-2, or Informix to get the transactions for 2% of the site? or do I use PHP/MySQL, have them pay the optional $200 registration fee to take care of the more heavily used 98% part of the site, and then write the code for the remaining 2% myself?
Which makes sense for the money?
-- ...Open Source isn't the only answer -- but it's almost always a better value than the alternatives...
Re: even more significant...
by
CodeShark
·
· Score: 2
Even more significant, you might be doing operations outside of the database (writing a file, sending an email) that if it fails, you need to roll back your DB transaction.
I'm not absolutely disagreeing with you but I'd bet that even in your example, I could design a process flow that would do the same thing in MySQL for when it was needed, and retain the speed advantage of MySQL the rest of the time. [Caveat: I'm limiting the above comment to RDBMS's running on single processor machines, because that's all I have used MySQL on so far.]
Anyone who thinks otherwise has never built a robust, scaleable system.
Oh Really??? What about those of us who were building systems that had to be robust and scalable before Oracle, et. al. had transactions? We did it with middle-ware or right in the application program code. Which is what you have to do with MySQL.
But wouldn't you know it, even though I've become a PDG Oracle SQL coder(pretty damn good [5 yrs experience], some of my non-Oracle applications (mostly based on Netware/B-Trieve) still blow the doors off of any SQL based system I've written since.
Transactions + SQL are not required for robustness and scalability. Transactional logic -- at whatever layer -- RDBMS, Middleware, or Application -- is, and no-one is disputing that.
BTW, the SQL advantage isn't necessarily performance anyway. It is and always has been that I can get at my data more ways with SQL without having to hand code my request for data in new ways into an application.
-- ...Open Source isn't the only answer -- but it's almost always a better value than the alternatives...
I'm currently doing a web site that uses some really big nasty SQL queries. I'm talking about nasty ones using at least 10 left joins, and using some pretty complex "WHERE" clauses.
Recently we hit a bug in MySQL that made the server hang. It turns out this bug had been found by other people and had been fixed in newer releases of the code. One problem down... but I think now we've hit another bug and this is in the newest release.
Based on the complexity of the design of my database, I have the impression that I'm seeing bugs that most other users don't see. But I am making sure to stay well within the stated limits of MySQL.
So my questions are:
Do other people use really big nasty queries with multiple left-joins or am I just a freak who should break things down more?
Is there any place to report MySQL bugs that only seem to pop up in PHP scripts?
How many of the MySQL limits are theoretical, and how many are tested / bug-free limits?
check the press release here. And like I said when the article was rejected, it's not opensource but at least it's not crippleware.
I think it's a shame though, as the backend of mysql seems to be much worse then postgreSQL.
"MySQL" considered capitalistic
by
Anonymous Coward
·
· Score: 3
The name "MySQL", which is clearly based on the concept of private ownership (as can be seen from the "My" part), is not acceptable for use in an Open Source environment. Such reactionary leanings are dangerous and can corrupt the minds of our youth.
In order to fix this problem, I recoment to Mr. Widenius et al to change the name to "OurSQL" or "ThePeople'sSQL". These new names will allow you to mesh better with the socialist Open Source Movement.
Thank you.
Re:"MySQL" considered capitalistic
by
mutagen
·
· Score: 3
It's just like Windows. My Computer. My Documents. Mine, mine mine.
Not to mention that the forthcoming Windows Millenium Edition (it's win9x, not nt/win2000) is called Windows ME!
Slashdot Mirror
Sorry, but Sybase implemented transactions and sp's before MS bought the code base at around version 5 of Sybase SQL Server. The two projects forked at that point.
Microsoft used to supply Sybase with the DOS version of isql.
Sybase ASE is still IMHO a superior product to MS SQL Server.
Matt. Want XML + Apache + Stylesheets? Get AxKit.
Even more significant, you might be doing operations outside of the database (writing a file, sending an email) that if it fails, you need to roll back your DB transaction.
:)
Transactions are essential. Anyone who thinks otherwise has never built a robust, scaleable system. Look at slashdot and you'll see what I mean
Matt. Want XML + Apache + Stylesheets? Get AxKit.
"As far as transactions specifically go, MySQL does support atomic operations which can be made to do essentially the same thing."
Oh, so you can roll back these atomic operations without having to write additional SQL to do deletes? And you can wait until all your inserts/updates are done before committing the changes to the RDBMS?
Why the world needs transactions: if you have an entity that spans, say, 3 tables, you're going to need 3 different SQL INSERT commands. If one of these commands fails, you need to back out the others. For instance, lets say you successfully inserted into 2 of the 3 tables. Your third insert fails. Without transactions, you have to manually go back and issue potentially expensive deletes. With transactions, you simply issue 'rollback' and poof, your data is back to normal.
--- [[NSNotificationCenter defaultCenter] addObserver:systemAdministrator selector:@selector(disableUserAccount:) na
Like i said before, if you have an entity that spans 2+ tables, you're going to need transactions. If you use triggers, you're going to need transactions. If you use foreign keys, you're going to need transactions.
A transaction treats any number of SQL statements essentially as one -- if one fails, they all fail.
If they all succeed, you can commit the transaction and have the data written out to the tables. Transactions are heavily used in conjunction with foreign keys, triggers, et al to support DATA INTEGRITY. Sure, MySQL may be quite fast, but if I have to reinvent the wheel of foreign keys and triggers, and the backend DB doesnt help me maintain data integrity, why would I care about how fast my queries are on completely irrelevant and stale data?
For example ( w/o a trigger ):
---[ CUT ]---
BEGIN TRANSACTION;
select nextval('trans_seq'); -- store this in a variable in your code
insert into billing (trans_id, account_id, customer_id, amount, trans_type) VALUES(*my trans_seq*, 1, 1, 45.00, 'D');
update account set balance=balance+45.00 where account_id=1;
COMMIT;
---[ CUT ]---
Lets say the second statement succeeded and the third ( update account ) failed. With transactions, you could NOT commit the data. Without transactions, your database is now shoring erroneous data; the account's balance is incorrect. Your query to pick up the account may be blindingly fast, but your data is erroneous -- and your company just lost $45.00 (in this transaction alone). Now apply this to an e-commerce site, where customers may order well over $1,000 at a time. Would you want to potentially lose $1,000 just so you can have your queries come back real fast, or would you want to sacrifice some speed in order to have your db essentially maintain itself? Bear in mind that I've had numerous queries under PostgreSQL come back much, much faster than they did under MySQL with the SAME DATASET. And PostgreSQL has transactions, views, foreign key support, triggers, etc.
--- [[NSNotificationCenter defaultCenter] addObserver:systemAdministrator selector:@selector(disableUserAccount:) na
I hear this all the time: "mySQL isn't a real database because it doesn't have transactions."
... blah blah , done) values ( blah blah , 'n');
....) values (order_id)
What's wrong with something like this:
insert into orders (
order_id = mysql_insert_id(mysql)
insert into line_items (order_id,
[repeat as necessary]
update changes set done = 'y' where id = order_id
Ignore / retry from logs any transactions where done = 'n'.
Isn't that all transactions amount to, at least for something that isn't a full-blown double-entry accounting system?
When I first started using Unix SQL databases, frankly mySQL was the only one I could afford. (I actually started using mSQL and converted to mySQL).
Now I work for a mid-sized company, and if we really wanted to we could probably afford Oracle. But it sounds to me that you can only get the performance out of Oracle you need by hiring an extremely expensive Oracle professional to run it. And, of course, I'd have to ascend a fairly high learning curve and rewrite all my scripts, since the API bears not even the slightest resemblance to mySQL's.
Since everyone wants these things done yesterday, mySQL was the only viable solution for my situation. And so far, it's worked perfectly, even with heavy load testing.
But I can't help but be concerned at all the people who say they're running into trouble with it at high volumes. These reports are a little vague for my taste; does anyone have real-life war stories?
D
----
Thanks, but I RTF website. I did not RTF todo list, as someone else has pointed out. I understand the speed vs. transactions tradeoff and agree that sometimes an application requires one more than the other. If you would RMF posting, you would see I was simply pointing out that, IMHO and in many other people's HO, transactions are an essential part of any SQL server, let alone the best SQL server.
But MySQL has no transactions. Many folks wouldn't even classify MySQL as a database system. Now I know he said "SQL server", not "database system", but the two are almost synonymous.
I wish the interviewer had followed up on this point. I like the speed of MySQL, but I can't take it too seriously due to the lack of transactions.
Best example I have -- I am currently coding an information web site for a small non-profit. 98% of the site is dynamically created (a la Slashdot), and the remaining 2% will require some heavy code in PHP to create the transaction type logic I've described elsewhere in this thread.
Now then, this info site might remain an unknown spot on the web, or it could suddenly get very very active like /. did about 18 months ago. Where suddenly I need all the speed I can get out of the database.
Now then, since I don't know how heavy the load will be ahead of time: do I ask the non-profit to pay the heavy, heavy license cost and support fees for Oracle, SQL Server, DB-2, or Informix to get the transactions for 2% of the site? or do I use PHP/MySQL, have them pay the optional $200 registration fee to take care of the more heavily used 98% part of the site, and then write the code for the remaining 2% myself?
Which makes sense for the money?
...Open Source isn't the only answer -- but it's almost always a better value than the alternatives...
I'm not absolutely disagreeing with you but I'd bet that even in your example, I could design a process flow that would do the same thing in MySQL for when it was needed, and retain the speed advantage of MySQL the rest of the time. [Caveat: I'm limiting the above comment to RDBMS's running on single processor machines, because that's all I have used MySQL on so far.]
Anyone who thinks otherwise has never built a robust, scaleable system.
Oh Really??? What about those of us who were building systems that had to be robust and scalable before Oracle, et. al. had transactions? We did it with middle-ware or right in the application program code. Which is what you have to do with MySQL.
But wouldn't you know it, even though I've become a PDG Oracle SQL coder(pretty damn good [5 yrs experience], some of my non-Oracle applications (mostly based on Netware/B-Trieve) still blow the doors off of any SQL based system I've written since.
Transactions + SQL are not required for robustness and scalability. Transactional logic -- at whatever layer -- RDBMS, Middleware, or Application -- is, and no-one is disputing that.
BTW, the SQL advantage isn't necessarily performance anyway. It is and always has been that I can get at my data more ways with SQL without having to hand code my request for data in new ways into an application.
...Open Source isn't the only answer -- but it's almost always a better value than the alternatives...
I'm currently doing a web site that uses some really big nasty SQL queries. I'm talking about nasty ones using at least 10 left joins, and using some pretty complex "WHERE" clauses.
Recently we hit a bug in MySQL that made the server hang. It turns out this bug had been found by other people and had been fixed in newer releases of the code. One problem down... but I think now we've hit another bug and this is in the newest release.
Based on the complexity of the design of my database, I have the impression that I'm seeing bugs that most other users don't see. But I am making sure to stay well within the stated limits of MySQL.
So my questions are:
check the press release here. And like I said when the article was rejected, it's not opensource but at least it's not crippleware.
I think it's a shame though, as the backend of mysql seems to be much worse then postgreSQL.
The name "MySQL", which is clearly based on the concept of private ownership (as can be seen from the "My" part), is not acceptable for use in an Open Source environment. Such reactionary leanings are dangerous and can corrupt the minds of our youth.
In order to fix this problem, I recoment to Mr. Widenius et al to change the name to "OurSQL" or "ThePeople'sSQL". These new names will allow you to mesh better with the socialist Open Source Movement.
Thank you.