On a table with lots of deletes spanning over the past six months, I have an **extremely** fragmented non-clustered columnstore index. It is about 500 GB and has 50 partitions. If I either rebuild it online, re-create it with DROP_EXISTING, or just drop and recreate it the hard way, it goes down to 80 GB. When testing this, I've observed the following: * If I drop the columnstore index and recreate it offline (no DROP_EXISTING, just drop and create), it takes one hour * If I re-create it with DROP_EXISTING offline, it takes two hours. * If I rebuild the columnstore index online, it takes two hours * If I reorganize the columnstore index without any special settings, I get bored and quit after five hours and see no evidence that the size of the index has decreased. * If I reorganize the columnstore index with COMPRESS_ALL_ROW_GROUPS = ON, then it completes after four and a half hours. The index does not get smaller. It gets 20 GB **bigger**. I'm not doing anything smart with the partitions. I'm using a one-liner to hit the entire index. I am on SQL Server 2022, Enterprise Edition. The clustered index is 2 TB rowstore. The biggest column in the table is 8 bytes. The non-clustered index only consists of two columns, one of which is the partitioning column. The biggest of column in the index is an int. I have seen no evidence that the tuple mover is disabled, so I cannot explain how the fragmentation got this bad. My only guess is that I happen to know that the rows were deleted based on the column that is not the partitioning column, so it could be the case that the alignment of the columnstore index is so terrible that no rowgroups crossed [whatever threshold it is that the background merge task uses](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16#reorganize-an-index) (I am unsure if that merge is part of the tuple mover or not). I have no idea why REORGANIZE made the columnstore index bigger. [Niko Neugebauer said during the pre-release for SQL Server 2016](https://www.nikoport.com/2015/12/25/columnstore-indexes-part-74-row-groups-merging-cleanup-sql-server-2016-edition/) that you have to invoke the tuple mover twice to really delete from a non-clustered columnstore index, but I did not think that behaviour even lasted until the real release of 2016 (I'm far past that, I'm on SQL Server 2022). My question is this: **Why would rebuilding a columnstore index online be dramatically faster than reorganizing it?** Would it have anything to do with the extreme fragmentation? For what it's worth, this same table has me suspicious of SQL Server [bugs](https://dba.stackexchange.com/q/345635/277982) such as [this](https://dba.stackexchange.com/q/345953/277982) .

We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have more rows and more partitions. Business would like to have some stats/data thanks to which will be able to decide If we can create index on production environment. So we prepare: - Logical reads before and after index for select queries and insert batches - CPU time (and elapsed time to check if we need parallelism) before and after index for select queries and insert batches - Duration of select queries and insert batches before and after index - Storage size of new indexes Do you have any advice ? Is there any list or process for that ?

I am working on cleaning up/optimizing indexes. I have run Find-DbaDbDuplicateIndex in PowerShell on one of my SQL Instances and identified 5 sets of "duplicate" indexes. 1 is a non-clustered index with only 1 key column, no includes, and it matches the key column of the clustered index. The non-clustered index does get a lot of reads, so I am kind of puzzled by why the non-clustered index there would get so many reads when the only column is the PK (The only thing that I can think of is if we have some odd query that only reads that column and doesn't look at any others on that table, or someone is pushing query hints through to use the non-clustered index, though I'd expect that to generate a ton of key lookups, which I do not see on that table). The rest of my duplicates are all non-clustered indexes where there is a unique non-clustered index and an identical non-unique non-clustered index, no included columns on either and key columns are identical. Here I also see the pattern of much higher reads on the non-unique over the unique non-clustered index, though I can at least rationalize that out by the constraints adding some overhead on the unique indexes. Before I start dropping indexes, are there any red flags or edge cases that I should look for in the workload that would suggest that I keep the duplicate non-unique non-clustered indexes?

I'm using MongoDB and want to create a partial index on the email field, but I only want it to apply to documents where the email field exists (i.e., email: { $exists: true }). How can I create this partial index? Here is what I have so far: db.collection.createIndex( { email: 1 }, { partialFilterExpression: { email: { $exists: true } } } ); When I use this index I don't add a new user without an email or an empty field Required: when I add an empty email or null or without email field then it should created many times but I received the error of duplication when I don't want to add an email with a partial Index Thanks in advance for your help!

I have a very high traffic database. In the application they will issue deletes, and frequently these deletes will deadlock with other deletes on the same table. I am researching on ways to remedy this, and one answer I saw was to ensure that the deletes have the fastest pathway to the record. Currently, all of the deletes follow this form: (@0 int) delete [dbo].[table] where (table_id = @0) Each of these tables has a primary key and clustered index on table_id. My question is, could adding a non-clustered index on table_id help speed these deletes up and prevent deadlocks from occurring? ---------- Deadlock graph: unknown unknown (@0 int)DELETE [dbo].[redacted_table] WHERE ([id] = @0) unknown unknown (@0 int)DELETE [dbo].[redacted_table] WHERE ([id] = @0) ---------- table definition: create table [dbo].[redacted_table]( [id] [int] identity(1,1) not for replication not null, [loan_id] [int] not null, [user_role_id] [int] not null, [assigned_by_user_id] [int] not null, [out_for_assignment] [bit] not null, [assignment_date] [datetime] not null, [recognize_date] [datetime] not null, [routing_source] [varchar](50) null, [request_guid] [uniqueidentifier] null, constraint [PK_redacted_table] primary key clustered ( [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 90) on [PRIMARY] ) on [PRIMARY] go alter table [dbo].[redacted_table] add constraint [DF_redacted_table_assignment_date] default (getdate()) for [assignment_date] go alter table [dbo].[redacted_table] add constraint [DF_redacted_table_recognize_date] default (getdate()) for [recognize_date] go alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_redacted_table3] foreign key([loan_id]) references [dbo].[redacted_table3] ([id]) go alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table3] go alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_user_redacted_table4] foreign key([user_role_id]) references [dbo].[user_redacted_table4] ([id]) go alter table [dbo].[redacted_table] check constraint [FK_redacted_table_user_redacted_table4] go alter table [dbo].[redacted_table] with check add constraint [FK_redacted_table_redacted_table5] foreign key([assigned_by_user_id]) references [dbo].[redacted_table5] ([id]) go alter table [dbo].[redacted_table] check constraint [FK_redacted_table_redacted_table5] go

I was executing a query to create a non clustered index in one of the table and that has around 43M of rows. It runs more than 1 hour, then I felt something is wrong and have cancelled the query execution. I refreshed the database and checked Indexes folder under the same table and there is no such index is created. When I try to run the same query again getting below error The operation failed because an index or statistics with name 'IX_My_Index_name' already exists on table 'My_Table_Name' When I try to drop the same index again getting below error cannot drop the index 'IX_My_Index_name' because it does not exist or you do not have permission Any suggestions what could have gone wrong here

When I was dealing with fragmentation, I had a question about a non-clustered index. In non-clustered index actual table data (typically stored in a clustered index, otherwise a heap) is stored separately from the non-clustered index. How does this impact table and index fragmentation when I create non-clustered index? I appreciate a detailed response.

Suppose that I have a table. Its primary key is clustered and consists of exactly one column. Is there ever a reason to _also_ make a non-clustered index on that same column with no other columns in any part of the non-clustered index?

I create transactional replication ,but forget to include non clustered indexes and now my subscriber have only clustered indexes (primary key) and I want to transfer all nonclustered indexes to subscriber.Based on this answer http://www.sqlservercentral.com/Forums/FindPost1325516.aspx is good idea to script all non clustered indexes on publisher and then execute it on subscriber or is there another way which is better ?

I have found large heap tables that are over-indexed and for example have multiple different nonclustered indexes with different columns, but some of those indexes have the (only one present) primary key column set as their first index column. I made some investigation, played around with indexes and queries and made my conclusions that I would like to see confirmed by professionals to be sure. Question 1: Am I right to say it is nonsense to have multiple indexes based on the primary key instead of only ONE index based on the primary key column as the first and ONLY index column, because all queries using this pk for filtering or joining return only ONE row? (Assuming additional filtered, joined or selected columns are put into the index as included columns to entirely cover the queries and avoid RID lookups) Question 2: Is it right to say that nonclustered indexes based on the primary key of a table should always be created as UNIQUE indexes? (a setting that is optional for whatever reason since sql server should already know that an index including the pk will be unique...)

SQL Server 2019 Index was created with online = on. Table has about 35000 rows with 7 columns with int, uniqueidentifier and datetime data types. Table has only clustered index. sp_whoisactive doesn't show any waits and locks during index creation process.

-sql
CREATE TABLE Table(
	ID uniqueidentifier NOT NULL,
	BeginTime datetime NOT NULL,
	EndTime datetime NULL,
	CreatePost uniqueidentifier NOT NULL,
	ClosePost uniqueidentifier NULL,
	OperationProtocol uniqueidentifier NOT NULL,
	StageType int NOT NULL,
 CONSTRAINT PK_Table PRIMARY KEY CLUSTERED 
(
	ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)

CREATE INDEX IXNC_Table ON Table(OperationProtocol, StageType) INCLUDE (BeginTime) WITH (FILLFACTOR=100, ONLINE=ON);

I stopped creation in ssms, but stopping took more than 5 minutes and killing session didn't help. I guess that the problem is in databases metadata but how can I check and fix it? P.S. Next day on a database with a low load, an index was created without any difficalties. P.P.S Can someone suggest which way to look to find the cause of this issue?

We have a primary table that drives most of the work within our product with many columns. Index 1 has a key consisting of: Column A, Column B, Column C, Column D Index 2 has a key consisting of: Column D, Column A, Column B The Includes are nearly identical, except Index 2 has Column C as an include when Index 1 has it in the key. Would Index 2 be a candidate to drop in favor of Index 1?

I have two UPDATE queries that are similar in structure, yet the SQL Server query plan for one shows indexes being used, and for the other it shows only a regular table scan. The following are the queries (as per the query plan, #1 does not use indexes, #2 does)- UPDATE Payment_Metadata SET Payment_Metadata.CommodityCode = 'RAW MATERIALS', Payment_Metadata.C1 = 'RAW MATERIALS', Payment_Metadata.C2 = 'INGREDIENTS', Payment_Metadata.C3 = 'OTHER ', Payment_Metadata.RuleText = '---', Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, Payment_Metadata.IsExcluded = 0, Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' FROM Payment_Metadata WHERE Payment_Metadata.IsProcessed = 0 AND (Payment_Metadata.EnrichedVendor = 'NFL' OR Payment_Metadata.Vendor_No = 'NFL') The second query uses an index: UPDATE Payment_Metadata SET Payment_Metadata.CommodityCode = 'RAW MATERIALS', Payment_Metadata.C1 = 'RAW MATERIALS', Payment_Metadata.C2 = 'INGREDIENTS', Payment_Metadata.C3 = 'OTHER ', Payment_Metadata.RuleText = '---', Payment_Metadata.LastUpdatedIndex = Payment_Metadata.LastUpdatedIndex + 1, Payment_Metadata.IsExcluded = 0, Payment_Metadata.LogText = 'Commodity>Raw Materials>Ingredients>Other' FROM Payment_Metadata WHERE Payment_Metadata.IsProcessed = 0 AND (Payment_Metadata.EnrichedVendor = '0202054' OR Payment_Metadata.Vendor_No = '0202054') The following is the table definition: CREATE TABLE [dbo].[Payment_Metadata]( [ID] [int] IDENTITY(1,1) NOT NULL, [Company_Code] [varchar](1024) NULL, [Comp_Code_Desc] [varchar](1024) NULL, [Vendor_Acct_Group] [varchar](1024) NULL, [Vendor_No] [varchar](10) NULL, [Vendor_Name] [varchar](1024) NULL, [Vendor_ABN] [varchar](1024) NULL, [Vendor_PTerm] [varchar](1024) NULL, [Vendor_PTerm_Desc] [varchar](1024) NULL, [Purchasing_Group] [varchar](1024) NULL, [Purchasing_Group_Des] [varchar](1024) NULL, [PO_DocType] [varchar](1024) NULL, [PO_DocType_Desc] [varchar](1024) NULL, [Purchasing_Document] [varchar](1024) NULL, [PO_Date] [varchar](1024) NULL, [PO_CreatedBy] [varchar](1024) NULL, [Plant] [int] NULL, [Item_Number] [varchar](1024) NULL, [Material_Number] [varchar](1024) NULL, [Material_Group] [varchar](7) NULL, [Material_Group_Desc] [varchar](1024) NULL, [Account_Assignment] [varchar](32) NULL, [Acct_Assignment_Desc] [varchar](1024) NULL, [GL_Account] [varchar](7) NULL, [GL_Account_Desc] [varchar](1024) NULL, [PO_Desc] [varchar](64) NULL, [PO_Quantity] [decimal](10, 2) NULL, [Order_UOM] [varchar](1024) NULL, [Order_Price_Unit] [varchar](1024) NULL, [Invoice_Receipt] [varchar](1024) NULL, [Invoice_Reference] [varchar](1024) NULL, [Invoice_Date] [datetime] NOT NULL, [Invoice_Scan_Date] [datetime] NULL, [Invoice_Item] [int] NULL, [Invoice_Amount] [decimal](15, 2) NULL, [GST] [decimal](10, 2) NOT NULL, [Invoice_Gross_Amount] [decimal](15, 2) NULL, [Currency] [varchar](1024) NULL, [Document_Type] [varchar](1024) NULL, [Document_Number] [varchar](1024) NULL, [Document_Date] [datetime] NOT NULL, [Posting_Date] [datetime] NOT NULL, [Payment_Term] [varchar](1024) NULL, [Baseline_Date] [datetime] NOT NULL, [Due_Date] [datetime] NOT NULL, [Payment_Document] [varchar](1024) NULL, [Clearing_Date] [datetime] NOT NULL, [CommodityCode] [varchar](128) NULL, [RuleText] [nvarchar](max) NULL, [IsProcessed] [bit] NOT NULL, [DataSource] [varchar](5) NULL, [IsContracted] [bit] NOT NULL, [IsPreferred] [bit] NOT NULL, [VendorRiskScore] [varchar](5) NULL, [EnrichedVendor] [varchar](128) NULL, [LastUpdatedIndex] [int] NOT NULL, [LogText] [nvarchar](max) NULL, [IsExcluded] [bit] NOT NULL, [C1] [varchar](50) NULL, [C2] [varchar](50) NULL, [C3] [varchar](50) NULL, [OriginalVendor] [varchar](60) NULL, [AdjustedAmount] [decimal](15, 2) NULL ) ON [PRIMARY] As you can see, the only change in the WHERE clause is the usage of numeric vs non-numeric characters (which I suspect should not impact the query plan?) There is one non-clustered index on each of the columns EnrichedVendor and Vendor_No. Any help would be appreciated. UPDATE: Including the query plans below, and I noticed that query #1 suggests an index (in green). That may be the solution to the answer.

I have the following tables: - User that has a non clustered Index on UserName and Active columns. - Notification that has a non clustered index on the UserId I don't understand why in the first situation the non-clustered index on the username is used, but non in the second: I expected for the second situation, that SQL will do a non clustered index seek on the User table, then a Clustered Index Seek on the Notification table. Link to the execution plan: https://www.brentozar.com/pastetheplan/?id=HyG1Kwm1h The IX_UserName index is defined as *unique* and it's also filtered on Active = 1.

I have a table with 150 million records and 8 indexes. One of the indexes is on userId. A lot of the current queries I have filter on userId and therefore this index works perfectly. For one of the queries, I have to expand the where clause to include regionId and productId columns. I have two options: I can create a new index with (userId, regionId, productId) or add these new columns to the existing index. Which would be a better option? If I modify the existing index, would it affect the other queries that already use userId? **My Goal:**
I want my current query which expands the where clause to include RegionId and productId to run faster but I don't want my other queries that only have userId in the where clause to be affected. If I add a new index on (userid, regional, productid) I can accomplish that but I'm not sure if I'm duplicating the index since I already have an existing index on userId which I can expand to include regionid and productId. But I'm not sure about the consequences of modifying the existing index. Thanks for your time!

I have a large (6db) trace table. It has a clustered key (DateTime) which is created through GETDATE(). The connection pool for connections to this database/table rises as high as 50 on average across a cluster of 10 computers, so on average we have at ~500 concurrent connections attempting to insert. The database fits in memory and hardly any IO is seen at all. I am trying to figure out whether under sustained INSERT load the clustered index gets to a point where it rebalances the tree, and whether this will cause a slowdown in the number of inserts that the system can sustain. There is some question in my mind as to whether the rebalancing an index is something SQL Server does on a clustered index (and even on a non-clustered index). Questions- 1. Are there any reasons for periodic/cyclic slow-down of insert performance? 2. Do rebalance operations automatically trigger on clustered indexes? 3. Do rebalance operations automatically trigger on non-clustered indexes? Other info - SQL Server 2008 - Really BIG server - 256Gb, 40 cores, 40mbit LAN...

We have a table partitioned on a two-year based hash function from the system date at the insertion time (let's call it PartitionKey). It also has a non-clustered index on a single field (Key). We insert millions of rows a day. If I run the below query, will it be resolved solely based on the non-clustered index on Key?

SELECT MAX(Key), MIN (Key) FROM MyTable

I have a large primary key clustered index I need to drop. I believe dropping it will also trigger index rebuilds on the nonclustered indexes (which are also pretty big). Is there a way to determine the progress of these processes?

## Current Situation I am observing a simple statement that is querying one table and accessing multiple indexes to retrieve the data:

SELECT DISTINCT 
feld16,
zahl4,
feld12,
feld19 FROM 
object1 WHERE 
(deleted = 0 or deleted IS NULL)

The query execution plan can be found on Brent Ozar's Paste The Plan website and a graphical representation follows: The table is made up of 82 columns containing various data. The distribution of the data in the deleted column is:

language:none 
 deleted | Number of records
---------+-------------------
       0 |        71'620'068
    NULL |                10
 a value |            59'673

The result set contains approx. 64 million rows of the approx. 71 million rows that match the search predicate WHERE (deleted = 0 or deleted IS NULL). This is because the DISTINCT omits 7 million records. ## Going Forward In order to speed things up a bit, I am considering adding a new index. Initially I thought I was knowledgeable enough to add an adequate index, but I am starting to second-guess myself. ## Question Which of the following index definitions would (possibly) be an adequate solution? ### 1. Index without INCLUDE

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[deleted] ASC,
	[feld16] ASC,
	[zahl4] ASC,
	[feld12] ASC,
	[feld19] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

### 2. Index with INCLUDE

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[feld16] ASC,
	[zahl4] ASC,
	[feld12] ASC,
	[feld19] ASC
)
INCLUDE 
(
	[deleted] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

### 3. Index with INCLUDE, but the other way round

CREATE NONCLUSTERED INDEX [IDXNew] ON [schema_owner].[object1]
(
	[deleted] ASC
)
INCLUDE(
    [feld12],
    [zahl4],
    [feld16],
    [feld19]
) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

## My Initial Thoughts .. were to go with the first index definition and include all the columns. On reading up on the INCLUDE part of the index creation I thought of creating the second index. I then thought: _Why not add a filter to the index like deleted = 0 or deleted IS NULL_ and then started second guessing my knowledge about indexes. ## Additional Information There are some trace flags running on the SQL Server 2016 instance. Some were recommended to us by Microsoft PFE during a PTOC. -- Assume fixed amount of memory -- Updates statistics in linear mode -- Enable QO fixes And the CE is currently set to run in backwards compatibility mode: CardinalityEstimationModelVersion="70"

I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index. I'm not sure why this could happen. **Here's my scenario:** **SETUP** Using MySQL 8.0.31 with transaction_isolation READ-COMMITTED I have this very simplified relationship where a phone number refers back to a person entity through a foreign key relationship: Schema is as follows: create table PERSON ( ID BIGINT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(100) NOT NULL); create table PHONE ( ID BIGINT PRIMARY KEY AUTO_INCREMENT, PERSON_ID BIGINT NOT NULL, PHONE VARCHAR(100) NOT NULL, constraint PERSON_ID_FK foreign key (PERSON_ID) references PERSON(ID)); **Data looks like this** PERSON: PHONE: **SCENARIO:** I then create two transactions that are running concurrently: **Transaction-71 does a series of insert / deletes for phone number against person #1** START TRANSACTION; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; insert into PHONE (PERSON_ID, PHONE) values (1, '416-111-1111'); delete from PHONE where PERSON_ID = 1; NOTE: It does not commit though.... **Transaction-72 does a series of insert / deletes for phone number against person #2** START TRANSACTION; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; insert into PHONE (PERSON_ID, PHONE) values (2, '416-222-2222'); delete from PHONE where PERSON_ID = 2; For a while this works as you might expect....neither transaction steps on each others because person_id is indexed and our inserts and deletes are based off this index. **But at some point (non-deterministic) one of the transactions suddenly tries to lock the other's primary index row on delete even though that row is not in the index for the person they sql is trying to delete** In this example below *delete from PHONE where PERSON_ID = 1;* results in the database trying to lock row with id 9. But that row is indexed to PERSON_ID = 2...so this query should not be trying to lock it?? select * from performance_schema.data_locks dl where OBJECT_NAME = 'phone'; And then it will eventually lead to deadlock. ===================================== 2022-11-22 15:41:34 0x7000052b9000 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 22 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 139 srv_active, 0 srv_shutdown, 30634 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 42 OS WAIT ARRAY INFO: signal count 42 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2022-11-22 15:41:12 0x7000042f2000 *** (1) TRANSACTION: TRANSACTION 2748, ACTIVE 3904 sec mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 14 row lock(s), undo log entries 12 MySQL thread id 27, OS thread handle 123145389051904, query id 2015 localhost 127.0.0.1 root updating /* ApplicationName=DBeaver 22.2.3 - SQLEditor */ delete from PHONE where PERSON_ID = 1 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table foo.phone trx id 2748 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000008; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050348; asc H;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000a; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 0100000105037e; asc ~;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000c; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 010000010503b4; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000e; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 010000010503ea; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000010; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050420; asc ;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 4 n bits 88 index PRIMARY of table foo.phone trx id 2748 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000009; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690151; asc i Q;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; *** (2) TRANSACTION: TRANSACTION 2753, ACTIVE 3893 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1128, 16 row lock(s), undo log entries 19 MySQL thread id 28, OS thread handle 123145390116864, query id 2053 localhost 127.0.0.1 root updating /* ApplicationName=DBeaver 22.2.3 - SQLEditor */ delete from PHONE where PERSON_ID = 2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table foo.phone trx id 2753 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000009; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690151; asc i Q;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000b; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690187; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000d; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 020000016901bd; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 800000000000000f; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 020000016901f3; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000011; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690229; asc i );; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000016; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 0200000169025f; asc i _;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000017; asc ;; 1: len 6; hex 000000000ac1; asc ;; 2: len 7; hex 02000001690295; asc i ;; 3: len 8; hex 8000000000000002; asc ;; 4: len 12; hex 3431362d3232322d32323232; asc 416-222-2222;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7 page no 4 n bits 88 index PRIMARY of table foo.phone trx id 2753 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32 0: len 8; hex 8000000000000008; asc ;; 1: len 6; hex 000000000abc; asc ;; 2: len 7; hex 01000001050348; asc H;; 3: len 8; hex 8000000000000001; asc ;; 4: len 12; hex 3431362d3131312d31313131; asc 416-111-1111;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 2789 Purge done for trx's n:o < 2789 undo n:o < 0 state: running but idle History list length 5 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421805873481168, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873479584, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873478792, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873478000, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421805873477208, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 2753, ACTIVE 3915 sec 6 lock struct(s), heap size 1128, 25 row lock(s), undo log entries 34 MySQL thread id 28, OS thread handle 123145390116864, query id 2104 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 880 OS file reads, 5566 OS file writes, 3904 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 3.01 writes/s, 2.43 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 4 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 0 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) 1.50 hash searches/s, 4.27 non-hash searches/s --- LOG --- Log sequence number 20296174 Log buffer assigned up to 20296174 Log buffer completed up to 20296174 Log written up to 20296174 Log flushed up to 20296174 Added dirty pages up to 20296174 Pages flushed up to 20296174 Last checkpoint at 20296174 Log minimum file id is 5 Log maximum file id is 6 1236 log i/o's done, 0.91 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 548441 Buffer pool size 8191 Free buffers 6972 Database pages 1212 Old database pages 427 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 919, not young 369 0.00 youngs/s, 0.00 non-youngs/s Pages read 856, created 359, written 2997 0.00 reads/s, 0.09 creates/s, 1.48 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1212, unzip_LRU len: 0 I/O sum:cur, unzip sum:cur -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=5057, Main thread ID=0x7000044fe000 , state=sleeping Number of rows inserted 229, updated 0, deleted 112, read 253 0.23 inserts/s, 0.00 updates/s, 0.45 deletes/s, 1.18 reads/s Number of system rows inserted 150, updated 455, deleted 93, read 65988 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ **Question is why is mySQL trying to acquire a lock on a record that is not indexed for person_id = 1 ??**