we have a scenario where User role is changes we have to update Account field then Account’s trigger will update 5 related objects so their triggers also fire 🙂 . Earlier we have used process builder at Account level but we are hitting CPU limit so we are switching to Asynchronous (via trigger) .
I’d like for a client session to lock a table, so that it can continue to read from and write to that table. At the same time other client sessions should only be able to read from the table. Once the lock is removed, then all client sessions should be allowed to read from and write to the table. Also once the lock is removed, all client sessions that tried to write to the table while it was locked should execute. I don’t want any errors thrown on attempted inserts during the lock.
I’d like for a client session to lock a table, so that it can continue to read from and write to that table. At the same time other client sessions should only be able to read from the table. Once the lock is removed, then all client sessions should be allowed to read from and write to the table. Also once the lock is removed, all client sessions that tried to write to the table while it was locked should execute. I don’t want any errors thrown on attempted inserts during the lock.
I have a PHP script (deleteAndReInsert.php) that deletes all rows where name = 'Bob', and then inserts 1000 new rows with name = 'Bob'. This works correctly, and the initially empty table ends up with 1000 total rows as expected.
We have a large table of about 100 million records and 100+ fields and there are frequent select and update queries running related to this table.
Now we have a requirement to set almost 50+ fields to null and we are planning to do this updation based on the primary key.
We are aware that there will be a locking mechanism when two updates are trying to update the same record.
I have InnoDB tables in Mysql, and I would know if when I exec an UPDATE my database automatically guarantees a lock or not.
I know that using the locks or MVCC in Mysql can achieve concurrency control, such as repeatable-reading. But I don’t know how MVCC avoids phantom-reading. In other places, I learned that it is generally implemented through MVCC and Gap-Lock, but what I currently understand is that MVCC does not need locks, that is, both updates and deletions are implemented using undo-logs. If so, how do MVCC and the lock mechanism work together?
I’m a bit confused with transactions vs locking tables to ensure database integrity and make sure a SELECT and UPDATE remain in sync and no other connection interferes with it. I need to:
I have a user table with field lastusedecnumber.
Is there a way to detect locked tables in MySQL? I mean tables locked by the LOCK TABLE table WRITE/READ command.
