New answers tagged

2

Unlike InnoDB, TokuDB historically did not automatically compute cardinality statistics. As a user you were required to manually run ANALYZE TABLE in order to calculate these values. All tables and indices created prior to 5.6.27-76.0 would also not maintain accurate row counts. After 5.6.27-76.0, new tables and indices, and tables that had RECOUNT ROWS ...


0

Your problem are the "idle in transaction" sessions. You should fix your application to avoid that. The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...


2

Deadlocks are not caused by a particular statement. It is caused by concurrency issues. So basically, you should start observing how one session of your application deal with from other sessions working concurrently. Here is a general guideline for avoiding deadlocks: Always maintain primary keys on tables. This primary key should be the means to identify ...


0

These locks can be selected from V$LOCK (or GV$LOCK for RAC) SELECT * FROM gv$lock WHERE lock_type = 'UL' AND '1073741825' like id1||'%' 1073741825 - is lockid column from dbms_lock_allocated table or you may get it by dbms_lock.allocate_unique('YOUR_LOCK_NAME',:lock_id);


2

Presuming the database is not actually "in-use" at the time this rebuild process starts Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-). Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB ...


4

Many of the issues you see are being caused by an inefficient execution plan: Not that the supplied plan and query matches the question, but even so, I'm working with what was provided. Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the ...


0

You can probably do a thousand simple commands on a MEMORY table per second. Can you, the single user, type fast enough to exceed that?? If you can, InnoDB may be the answer. In at least one test, high speed ingestion worked better with InnoDB, presumably because of the differences in locking. Be aware that data varchar(64000) NULL is not efficient in ...


0

You said in your question It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct? You are correct. The MEMORY storage engine performs a full table lock for every DML statement. That is why the link you mentioned ...


6

As Paul White answered absolutely correct there is a possibility for temporarily "skipped" identity rows. Here is just a small piece of code to reproduce this case for your own. Create a database and a testtable: create database IdentityTest go use IdentityTest go create table dbo.IdentityTest (ID int identity, c1 char(10)) create clustered index ...


17

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction? Yes. The allocation of identity values is independent of the containing user ...


1

Really peculiar behaviour, unfortunate this limitation still exists atleast in my version of MariaDB: MariaDB [test]> select @@version; +-----------------+ | @@version | +-----------------+ | 10.0.23-MariaDB | +-----------------+ 1 row in set (0.00 sec) MariaDB [test]> create table t ( x int not null ); Query OK, 0 rows affected (0.05 sec) ...


2

MVCC applies to isolation levels read-committed and repeatable read (default). You don't need to specify anything for both of these features to work together. Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.


4

The answer really depends largely on your isolation level, what type of locks are held by the transaction being rolled back, and what your non-rollback session is trying to do. Step 1) What is locked? Before the ROLLBACK was issued, that transaction had done work and acquired locks. You can see details on those locks by looking in sys.dm_tran_locks: ...


2

You should look at this from two different points of view - that of the connection that issued the ROLLBACK and that of all other connections. The issuing connection is easy: no, you do not have access. The ROLLBACK is performed synchronously. This connection cannot do anything else until the ROLLBACK succeeds. (And if it fails for any reason you're in a ...



Top 50 recent answers are included