Menu
Is this "compare and swap" statement always atomic regardless of engine (e.g. InnoDB or MyISAM)? :
UPDATE tbl_name SET locked=1 WHERE id=ID AND locked <> 1;
I ask this because I intend to use this statement to do pseudo row-level locking that is compatible with both transactional and non-transactional database tables.
This is the method that is recommended for MyISAM, but I am uncertain as to whether this works for InnoDB since the documentation suggests using transactions instead.
656
The performance of InnoDB for large volumes of data is better as compared to MyISAM. MyISAM doesn't support transactional properties and is faster to read. As compared to InnoDB, the performance for a high volume of data is less.
MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM's readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table.
In terms of data queries (SELECT), InnoDB is the clear winner, but when it comes to database writes (INSERT and UPDATE), MyISAM is somewhat faster. However, the lower speed of InnoDB is more than compensated for by its transaction protocol.
Yes. In InnoDB, the row will be locked (make you have an unique index on id, the update locks all rows it has to scan), updated and the lock released. If you are not in an explicit transaction / auto-commit is on, each statement is run in its own transaction, but it's still a transaction and lockings are performed
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
