3 ms·

MySQL is not too hot on Transactions

by Devilboy 15y ago

mootothemax 15y ago
MySQL != MyISAM. Check out InnoDB :)
- moe 15y ago
  InnoDB isn't too hot on transactions either. Google for "InnoDB deadlock".
  - mootothemax 15y ago
    I've googled, but am seeing the same kind of results that I see when I try "oracle deadlock", "postgresql deadlock" and "db2 deadlock." Still being relatively new to InnoDB, what particular deadlock dangers make it more troublesome than other DBMS engines?
    - pkteison 15y ago
      There are three possible ways that I know of to handle concurrency issues on shared data: Use some sort of a journaling approach (google keywords 'snapshots' or MVCC), or use locks, or ignore the problem. If it ignores the problem, it's not a database. Locks suck for volume. Locks cause much more deadlock than other options. Locks are fast in the simple case. Locks are easier to program and take less resources. InnoDB uses locks. SQL Server also defaults to locks. People often specify 'ignore the problem' mode (nolock/read uncommitted). There is a new journaling approach available, but it was only introduced in 2005 and I don't think many people are using it yet. Which is a shame, it's a great feature. Oracle and Postgres both do a journaling approach. They will have less deadlock problems because readers and writers don't need to block each other. With InnoDB or default sql server, read locks block writes, which sucks. See http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.htm...
      - mbirk 15y ago
        By default, InnoDB doesn't acquire read locks when doing queries. It "runs queries as nonlocking consistent reads by default, in the style of Oracle." See http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-mo...
        pkteison 15y ago
        Wow, looks like I got that completely wrong w.r.t. innodb specifically. Wish I could still edit my first post. :( Thank you. http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-rea...

MySQL is not too hot on Transactions

mootothemax 15y ago

MySQL != MyISAM. Check out InnoDB :)

moe 15y ago
InnoDB isn't too hot on transactions either. Google for "InnoDB deadlock".
- mootothemax 15y ago
  I've googled, but am seeing the same kind of results that I see when I try "oracle deadlock", "postgresql deadlock" and "db2 deadlock." Still being relatively new to InnoDB, what particular deadlock dangers make it more troublesome than other DBMS engines?
  - pkteison 15y ago
    There are three possible ways that I know of to handle concurrency issues on shared data: Use some sort of a journaling approach (google keywords 'snapshots' or MVCC), or use locks, or ignore the problem. If it ignores the problem, it's not a database. Locks suck for volume. Locks cause much more deadlock than other options. Locks are fast in the simple case. Locks are easier to program and take less resources. InnoDB uses locks. SQL Server also defaults to locks. People often specify 'ignore the problem' mode (nolock/read uncommitted). There is a new journaling approach available, but it was only introduced in 2005 and I don't think many people are using it yet. Which is a shame, it's a great feature. Oracle and Postgres both do a journaling approach. They will have less deadlock problems because readers and writers don't need to block each other. With InnoDB or default sql server, read locks block writes, which sucks. See http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html http://dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.htm...
    - mbirk 15y ago
      By default, InnoDB doesn't acquire read locks when doing queries. It "runs queries as nonlocking consistent reads by default, in the style of Oracle." See http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-mo...
      - pkteison 15y ago
        Wow, looks like I got that completely wrong w.r.t. innodb specifically. Wish I could still edit my first post. :( Thank you. http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-rea...