Transaction Locking and MySQL

[esc]

Hi,

Can somebody please explain to me, what does the fact that MySQL does not support transaction locking mean for say an online-shop. Can only one single person at a time do a transaction and a second person who happens to hit the submit button during the time the first transaction takes place has to wait? Gets an error? Hopefully the transactions are not intermingled so that the first person pays the bill of the second one. Are the whole involved tables locked? Or how does this work in MySQL compared to databases with transaction locking as Oracle, Adabas, etc. And what are the drawbacks when using MySQL as DB-backend for a shop? Is it slower, less secure, or what ever?

Erich

[Bruce]

Transactions are necessary when you need to update multiple tables and have all the updates appear to have happened "all at once".[nbsp][nbsp]For example, if a customer is making a purchase, you want to decrement the number of that item that is in stock while adding one of that item to his purchase list all in one step.[nbsp][nbsp]The only way to do this in MySQL is to lock the appropriate tables, which will cause other clients to have to wait while you're doing your stuff.[nbsp][nbsp]Admittedly, this wait will typically be very short (fraction of a second).[nbsp][nbsp]I believe MySQL now allows row-level locking, which means you'd only be locking one row of the table instead of the whole table.

The other, and probably more important, feature that transactions provide and locking doesn't is "transactional safety".[nbsp][nbsp]This means that if you experience a crash while performing the multiple steps, they will either all be done or none will be done when the system comes back up.

Databases like Oracle and PgSQL (and probably Adabas, although I know less about it) can do this with minimal locking.[nbsp][nbsp]Both the first two do transactions with zero locks, but you would still need a row level lock on the inventory count due to the read-modify-write cycle.

The drawbacks are not necessarily speed, at least not for a small shop.[nbsp][nbsp]MySQL is one of the fastest SQL engines out there for simple queries and small numbers of clients.[nbsp][nbsp]It's not security either.[nbsp][nbsp]MySQL provides security similar to other systems, just in different ways.[nbsp][nbsp]The biggest drawback that I see is in the data integrity that MySQL doesn't provide.
------------------
Bruce Guenter, FutureQuest
http://untroubled.org/

[Terra]

Before _I_ can discount MySQL in regards to the Transactions and Row locking I feel it prudent to take a long deep read into:
http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#Table_types

Mostly, it's selecting the right Table type and one can see that MySQL is heading in the direction to fulfill many of it's current limitations...[nbsp][nbsp]However, I will agree it is nowhere near as robust as Oracle (insert any large $$$ DB here) or PostgreSQL...

In light of all this, I believe that the MySQL 4.x series is going to turn many of it's current limitations around as the path has already been defined...

In the end, I feel that MySQL provides blazing speeds along with the most bang-for-the-buck for the vast majority of site owner needs...[nbsp][nbsp]If you need all the extra stuff that only extremely complex DB's offer, then most likely your needs will _not_ be fulfilled by 'Community Hosting'...

--
Terra
--Don't count MySQL out yet, even though many will try to convince you otherwise--
FutureQuest

[MarkW]

Terra
Which MySQL table types (other than MyISAM) does Futurequest support?
Mark

[This message has been edited by Mark Wolfgramme (edited 06-07-01@06:01 am)]

[Terra]

Current table types:

MyISAM (new and default)
ISAM (older)
HEAP
MERGE

We do not support the newest table types due to their instability and potential to crash MySQL daemon threads...
BDB (Berkeley)
GEMINI
InnoDB

We probably won't touch those until the MySQL 4.x series is announced as stable...

--
Terra
--Stability first, power second - though I try my darndest to balance both--
FutureQuest

[melorama]

We do not support the newest table types due to their instability and potential to crash MySQL daemon threads...
BDB (Berkeley)
GEMINI
InnoDB

We probably won't touch those until the MySQL 4.x series is announced as stable...

Which (if the time it took for 3.23 to be declared stable is any indication) will most likely be after the dreaded Unix "2038 problem" has caused mass hysteria among computing professionals :P