Tag Info

New answers tagged

2

This issue was well dealt with in this post by Yves Trudeau who seems to suggest that it is safe - his conclusion is that Conclusion Like ZFS, ext4 can be transactional and replacing the InnoDB double write buffer with the file system transaction journal yield a 55% increase in performance for write intensive workload. Performance gains are ...


1

The only situation I can think of is reloading a large mysqldump. Why ? Check out this Pictorial Representation of InnoDB (Percona CTO Vadim Tkachenko) From the picture, you can see that the InnoDB Buffer Pool writes dirty pages to Log Buffer Insert Buffer in ibdata1 Double Write Buffer in ibdata1 .ibd file for each InnoDB table Shutting off the ...


1

You should use SELECT ... LOCK IN SHARE MODE. Why ? SELECT ... LOCK IN SHARE MODE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and ...


0

The problem turned out to be a shp2pgsql process that was run in a loop in a shell script in screen that finished successfully several hours before I posted this question. It did not show up in pg_locks, nor pg_stat_activity, as it is a separate process, which was then being piped into psql. Killing this window inside screen, allowed the ALTER TABLE ...


1

Per the notes given on the wiki for that query, it only shows row level locks. ALTER TABLE takes a table level ACCESS EXCLUSIVE lock. One or more transactions will be holding weaker locks on the table that prevent ALTER TABLE from acquiring its lock. You can identify the lock being waited on with something like: select * from pg_stat_activity sa inner ...


0

What operation(s) are you trying to perform on the table? Some DML? Maintenance (eg, reorg, (re)indexing, adding/changing RI)? Modifying DDL? If just doing DML, consider: begin tran lock table <table_name> in exclusive mode --perform DML-- commit (rollback?) tran 'lock table/exclusive mode' (attempts to obtain an exclusive table-level lock), once ...


3

InnoDB isolation levels are not "bad" or "good", it depends on what your application requires in terms of isolation. In your example, by using repeatable read, you make sure that once the first select has been done, no other transaction can modify the state of the database, from the point of view of that transaction. In other words, it is as if you had run ...


2

ASSESSMENT Your query is kind of dangerous to the MySQL Query Optimizer. I have an old post (Problem with MySQL subquery). The question posed involved this query DELETE FROM test WHERE id = (SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL ...


0

If you are using InnoDB, You can secure your transaction with SELECT ... FOR UPDATE sentence http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html With this, you can perform an exclusive lock on the row while your transaction is not finished. Other sessions will wait until the row lock is released.



Top 50 recent answers are included