We want to add new field into table but table got lot its take 3 min on stage env but in production its taking more then 10 min(we killed the process after this)

ALTER TABLE posts_topic ADD COLUMN hindi_description longtext NULL, LOCK=NONE;

SHOW FULL PROCESSLIST;
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id      | User            | Host               | db            | Command | Time    | State                           | Info                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       5 | event_scheduler | localhost          | NULL          | Daemon  | 6044590 | Waiting on empty queue          | NULL                                                                                                                                                                                                                                                                          |
| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1678479 | admin           | 172.31.32.92:47982 | orchestra_cms | Sleep   |    8465 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1680873 | admin           | 172.31.32.92:60246 | orchestra_cms | Query   |     286 | Waiting for table metadata lock | ALTER TABLE posts_topic ADD COLUMN hindi_description_11 longtext NULL, LOCK=NONE                                                                                                                                                                                          |
| 1680907 | admin           | 172.31.32.92:51524 | orchestra_cms | Query   |       0 | init                            | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                         |
| 1681014 | admin           | 172.31.32.92:51602 | orchestra_cms | Query   |     135 | Waiting for table metadata lock | SELECT (1) AS a FROM posts_posttopic INNER JOIN posts_topic ON (posts_posttopic.topic_id = posts_topic.id) WHERE (NOT (posts_posttopic.object_status = 0) AND posts_posttopic.post_id = 'kuqDLdvyodbd' AND posts_topic.name LIKE 'TOPNEWS') LIMIT 1 |
| 1681019 | admin           | 172.31.32.92:43246 | orchestra_cms | Sleep   |     135 |                                 | NULL                                                                                                                                                                                                                                                                          |
+---------+-----------------+--------------------+---------------+---------+---------+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 13578859
Purge done for trx's n:o  0;
+---------------+-------------+--------+-------------+
| Database      | Table       | In_use | Name_locked |
+---------------+-------------+--------+-------------+
| orchxxxxx_xxx | posts_topic |      1 |           0 |
+---------------+-------------+--------+-------------+

CREATE TABLE posts_posttopic (
  id int NOT NULL AUTO_INCREMENT,
  object_status smallint NOT NULL,
  leaning smallint unsigned DEFAULT NULL,
  leaning_direction smallint unsigned NOT NULL,
  created_on datetime(6) NOT NULL,
  post_id varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  topic_id varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id),
  KEY posts_posttopic_post_id_73ce25a9_fk_posts_post_id (post_id),
  KEY posts_posttopic_topic_id_04b92641_fk_posts_topic_id (topic_id),
  CONSTRAINT posts_posttopic_post_id_73ce25a9_fk_posts_post_id FOREIGN KEY (post_id) REFERENCES posts_post (id),
  CONSTRAINT posts_posttopic_topic_id_04b92641_fk_posts_topic_id FOREIGN KEY (topic_id) REFERENCES posts_topic (id),
  CONSTRAINT posts_posttopic_chk_1 CHECK ((leaning >= 0)),
  CONSTRAINT posts_posttopic_chk_2 CHECK ((leaning_direction >= 0))
) ENGINE=InnoDB AUTO_INCREMENT=1945751 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE posts_topic (
  object_status smallint NOT NULL,
  id varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  post_count int NOT NULL,
  display_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  slug varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  image_url varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  description longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  is_qualified tinyint(1) NOT NULL,
  created_by varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  created_on datetime(6) NOT NULL,
  updated_on datetime(6) NOT NULL,
  is_section int NOT NULL DEFAULT '0',
  is_language smallint NOT NULL DEFAULT '0',
  PRIMARY KEY (id),
  KEY posts_topic_slug_8ebc1796 (slug),
  KEY is_section (is_section)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

If I clone the table posts_topic as posts_topic_copy and add field its take no time. might be issue with foreign key between posts_topic and posts_post_topic table. Im not sure why but as i killed this processesi its worked

| 1511667 | admin           | 172.31.32.92:52604 | orchestra_cms | Sleep   |    1221 |                                 | NULL                                                                                                                                                                                                                                                                          |
| 1589284 | rdsadmin        | localhost          | NULL          | Sleep   |      11 |                                 | NULL                                                                                                                                                                                                                                                                          |

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to use NOLOCK in every single query. Now, I work with a DBA who has banned NOLOCK under any circumstance - even when a report of mine (due to a considerable lack of indexes on a couple of tables) is stopping replication and system updates. In my opinion, in this case, a NOLOCK would be a good thing. Since most of my SQL training has come various DBAs with very different opinions, I wanted to ask this to a wide variety of DBAs.

I have an application where each page load inserts information about the visit into a MariaDB table. There is an analytics hub where statistics from this data can be viewed. In the beginning, this used to work well, but now the table is large enough (currently 650,000 rows), where performing SELECT queries on it with lots of filters tends to take a while, sometimes as long as 20 seconds. This in and of itself isn't an issue. The problem is that since all pages insert records into this table, whenever analytics are being retrieved, the whole site is basically down until the query finishes. At that point, everything else continues again. This is admittedly not a great architecture for this purpose, but I would like to modify some things so that the analytics queries are not locking the table. By this, I mean simple modifications, not setting up a read replica and point the analytics queries at that. Is it possible to change the queries so that the SELECT doesn't lock the table in any way that would prevent the INSERTs from succeeding. The INSERTs are all auto-increment, and I don't care if a little accuracy is sacrificed on the SELECT. I tried moving this particular table into its own table, but that hasn't really helped in, since it is, in fact, this specific table which is the issue, not access to other tables in either database. I have tried some of the approaches such as in this answer , but they don't work because they are focused on SELECT. I don't care if the SELECT's are slow, but the INSERTS all queue up while a SELECT is being run and this is causing serious issues whenever this occurs. Speeding up the query might help a little bit, but fundamentally I want to prevent the INSERTs from waiting on the SELECTs to complete. Is this possible to do, without changing the database setup (e.g. using separate tables for SELECT/INSERT)? I am using MariaDB 10.3 with the InnoDB engine. This is the structure of the table: views | CREATE TABLE views ( id int(10) unsigned NOT NULL AUTO_INCREMENT, datetime datetime NOT NULL DEFAULT current_timestamp(), userid int(11) unsigned NOT NULL, sessionid varchar(64) NOT NULL, ip varchar(64) NOT NULL, agent varchar(256) NOT NULL, domain varchar(32) NOT NULL, pagebase varchar(256) NOT NULL, pagefull varchar(256) NOT NULL, hash varchar(128) NOT NULL, ref varchar(256) NOT NULL, PRIMARY KEY (id), KEY userid (userid), KEY pagebase (pagebase), KEY pagebase_2 (pagebase,domain), KEY hash (hash) ) ENGINE=InnoDB AUTO_INCREMENT=930146 DEFAULT CHARSET=utf8mb4 Here is one of the faster queries (really), which takes 14 seconds to run: SELECT domain, COUNT(DISTINCT(hash)) as d, COUNT(*) AS c FROM views GROUP BY domain ORDER BY d DESC; Some of the much queries use lots of wildcard searches, which, even on indexed columns, perform quite badly. Again, I have no issue with these queries taking a while, but whenever they are running, they block everything else, which is a serious issue.

We run SQL Server 2000 and we get a few of these errors every night. Could not continue scan with NOLOCK due to data movement The query that throws this error is a large complex query that joins over a dozen tables. Our underlying data can be updated frequently. The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints. Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :) PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

NOLOCK results in an allocation order scan rather than an index order scan. NOLOCK doesn't block writes because it doesn't take shared locks on the table. During the NOLOCK's scan, since there are no shared locks, and a write (insert/update) happens prior to the point the scan has currently reached, then this situation will cause missing records. Similarly, during a write (insert/update), when page split happens after a row is read, and that row now is part of the next page (due to the page split), then this situation will cause duplicate records. I have been reading articles which seem to indicate that the missing/duplicate row problem is due to allocation order scans. 1. As shown in my examples above, the missing/duplicate records problem is cause due to no lock and page splits. Correct? Can it also be caused due to allocation order scans? 2. If allocation ordered scan can indeed cause missing rows/duplicates, then I want to ask- suppose the engine had used an index order scan (I know that the engine won't do this but just assume for the sake of this question) rather than the allocation order scan, then how will it have solved the missing/duplicate rows problem?

Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100. If a transaction performs "update Bank_Account set balance = 200 where 1=1; commit;" Is it possible for any 2 transactions performing a select on the Bank_Account table to have one read 200 and another one read 100 for a different bank account? Or once 200 is returned for a given bank account, all others necessarily return 200 too because the commit was atomic? My database intuition would be that once one reads 200, all others must read 200 because a transaction is atomic, but i also read that some selects can execute without locking the table or rows and return a snapshot of data at the start of a transaction, even assuming read committed isolation level. If that is the case, how do databases implement this behavior without locks? What magic does the commit and the selects perform to assure the selects operate on an atomic, all at same time, behavior of a commit? Thanks

I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked? Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that makes the circumstances non-normal. In this case, **I don't care at all about ACID**. The only thing that I care about is not cancelling the query that has TABLOCKed the table. I've tried WITH (NOLOCK), but signs suggest that it does not work. Am I asking for the impossible? Just to spell it out, suppose that I'm running

INSERT [dbo].[Table1] WITH (TABLOCK)
SELECT * FROM [A_Lot_Of_Rows]

My goal is to query Table1 while that runs. I'm doing bulk inserts and, while they're running, have decided that I want to check some detail. I don't care what row I get said detail from. Each row in A_Lot_Of_Rows has a date in it. A_Lot_Of_Rows is truncated by my stored procedure and it is repopulated in date-based batches before being truncated again. By reading an arbitrary row from the table, I can tell what batch I'm on.

Most of the select statements in our stored procedures use WITH(NOLOCK) and I was told by my co-workers that they use it to improve performance. Now, I know that this hint helps with locking but does it actually improve performance? I've read somewhere that we shouldn't use the NOLOCK hints in OLTP databases but I see the opposite being done at my workplace. Could someone shed light on the best practices when it comes to using the NOLOCK hints?

Background: I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively. Problem: I need to do a data extract from the database for reporting purposes. This will involved joining the three tables and writing each row to a text file. This database is currently taking production traffic and I want to limit performance impact. This data extract should not prevent reads, writes, or updates from occuring during the course of the process. From the research I have done, I believe adding the 'NOLOCK' hint to the data extract query will allow the production traffic to behave normally while still accomplishing my primary task of the data extract. I'm assuming the dirty reads associated with the 'NOLOCK' hint will be isolated to the data extract query. Am I correct to assume that the 'NOLOCK' hint on the data extract query will only affect that one particular query? Does it affect all queries being executed on the database while the data extract query is executing? Is there a better approach to accomplishing my goal than what I've presented above?

Link: https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/ The article mentions some problems with using NOLOCK: - You can see rows twice - You can skip rows altogether - You can see data that was never committed - Your query can outright fail with an error, “could not continue scan with nolock due to data movement” Then a fix is mentioned as: > Create an index on the table (any single-field index would have worked > fine in this particular example, giving SQL Server a narrower copy of > the table to scan) How does creating an index help with NOLOCK problems?

## Scenario I have a table that deals with concurrent SELECT's and DELETE's. I'm getting a few deadlocks on my SELECT statements. I assume that the DELETE from the other transaction is getting an exclusive lock and conflicting with the shared lock of the SELECT. ## Details - SQL Server 14.00.3281.6.v1 - Running on AWS RDS ## Use Case - My app can trigger a SELECT in a variety of ways. - If the app triggers a DELETE, it will (always) then trigger a SELECT to retrieve the results that reflect the effects of the DELETE. In the case of a concurrent SELECT and DELETE, it might look like this (drawn in MS Paint, because I try to be professional...): Edit: By "trigger" above I don't mean an actual db trigger, but just application behavior. ## Research I poked around here on the DBA Stack Exchange and found that I could SELECT myTable WITH (NOLOCK) which would prevent the shared lock. I'm considering using this, but I know there are lots of caveats and gotchas so I want to validate my decision or replace it if necessary. I'm new to WITH (NOLOCK) so here's what I've learned from this helpful site : > the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish its processing. ## Justifications These quotes are from the same link. Under each one I've described my thinking in concluding that the behavior won't affect me. > In general, using explicit table hints frequently is considered as a bad practice that you should generally avoid. For the NOLOCK table hint specifically, reading uncommitted data that could be rolled back after you have read it can lead to a Dirty read, which can occur when reading the data that is being modified or deleted during the uncommitted data read, so that the data you read could be different, or never even have existed. Dirty Read: I don't think I need to care about this because the SELECT is not actually invalid because of the dirty read. Any DELETE that caused a dirty read will then trigger a new SELECT that will correct the final result. **I consider both SELECT results valid, though one was only valid for a few milliseconds.** > The WITH (NOLOCK) table hint also leads to Nonrepeatable reads; this read occurs when it is required to read the same data multiple times and the data changes during these readings. In this case, you will read multiple versions of the same row. Nonrepeatable Read: My SELECT only has one SELECT statement from that table, so I assume this isn't an issue. > Phantom reads can be also a result of using the WITH(NOLOCK) table hint, in which you will get more records when the transaction that is inserting new records is rolled back, or fewer records when the transaction that is deleting existing data is rolled back. Phantom Read: Same as Dirty Read > Another problem that may occur when other transactions move data you have not read yet to a location that you have already scanned, or have added new pages to the location that you already scanned. In this case, you will miss these records and will not see it in the returned result. If another transaction moves the data that you have already scanned to a new location that you have not read yet, you will read the data twice. I'm not sure if this affects my use case or not, but I assume just deleting a few rows isn't going to cause this level of page reshuffling. I really don't know what I'm talking about on this topic though, so maybe I'm way off. ## Question Is this use case one of those rare ones that actually can make safe use of WITH (NOLOCK) or is there still some danger I should consider? ## Initial Results While I was writing this question I just threw it into Dev to try it, and it seems that there are still deadlocks happening somewhere. I'd still like to understand if this approach is valid and if it may still be part of the overall deadlock solution. ## Other Ideas I found this suggestion to use the SNAPSHOT isolation level, but I am unsure if I should have to bear the performance penalty when my use case can withstand side effects like dirty reads. Disclaimer: I'm not a DBA, but a software dev with a few years of DB experience. I'm only "textbook-level" familiar with the inner workings of locking, pages, hints, etc. so please bear with me and let me know if I can improve the question in any way. I'm happy clarify if needed.

I am researching the harm of using NOLOCK SQL to load data from one database that is actively used into a reporting database. I understand that there are problems with using NOLOCK but I am thinking of ways to counter them using the strategy explained below. I understand that there are better ways like log shipping, replication, mirroring, AG, clustering to have a replica database, but those are not the point of this question. The target DB has a history table that holds the LoadDate. Every 10 minutes, the scheduler runs a SELECT query (with NOLOCK) with WHERE clause based on a timestamp to fetch the data, dump it into a staging table, remove duplicates if any (keep latest) and merge it into the target table. The two tables that are used in the SELECT query may be concurrently modified when the SELECT query is running, but the join criteria's column values won't change. Example pseudocode: DECLARE @LASTLOADDATE=SELECT MAX(LOADDATE) FROM HISTORYTABLE; DECLARE @CURRENTDATE=GETDATE(); SELECT C1,C2,C3 FROM TBL1 T1 WITH(NOLOCK) JOIN TBL2 T2 WITH(NOLOCK) ON T1.ID=T2.T1_ID WHERE T1_TIMESTAMP>@LASTLOADDATE AND T1_TIMESTAMP<=@CURRENTDATE //There is no index on timestamp column but an index may be added in the future INSERT THE ABOVE RECORDS INTO STAGING TABLE REMOVE DUPLICATES MERGE DATA FROM STAGING TABLE INTO TARGET TABLE INSERT INTO HISTORY TABLE THE VALUE FROM @CURRENTDATE 1. NOLOCK results in dirty reads. But there are no transactions in my database/application so this is not an issue. 2. NOLOCK causes non-repeatable reads and phantom reads, which is also fine because my SELECT query is not running inside a transaction. 3. Returning duplicates (because of page splits and allocation order scan) is not a problem because I stage the data, use row_number partition by timestamp field to keep the latest record and then do a SQL MERGE with target table. So partitioning and picking latest record handles the duplicates issue. Alternatively, this record will have its timestamp updated (because it was updated after the SELECT is run), so the latest value will get picked up by the subsequent scheduler run anyways. 4. Missing records (because of allocation order scan) can happen when select has read past a point after which a record is inserted (or caused to get inserted due to an update) prior to that point. When the query runs, in this scenario it will miss the record. But since the records have a timestamp then it will get picked up in the next run. 5. Corruption issues happen only when NOLOCK is used with INSERT/UPDATE, whereas I am using only SELECT so this is not an issue. Is any of my explanations incorrect and are there any other problems that I fail to see?

Have you ever had to justify NOT using a query hint? I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on by default because they hate seeing it in their code thousands of times. I tried to explain that it is allowing dirty reads and they will end up with bad data eventually, but they believe the performance tradeoff is well worth it. (Their database is a mess; no wonder they have performance issues.) If you have a clear example of how to present the case against this abuse of the NOLOCK hint, that would be appreciated.

Is it possible to stimulate a situation where rows either get skipped or get read multiple times in a select statement? E.g. the way we can cause a blocking or a deadlock...

We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is: >*Could not continue scan with NOLOCK due to data movement...* This question says this error is thrown when a process reads data which is being deleted by another process. Our process is deleting and reading the same rows, however one after the other as per following query (the SELECT is after the DELETE): DELETE FROM Table1 WHERE colum1 = somevalue1 AND column2 = somevalue2 SELECT COUNT(*) FROM Table1 WITH (NOLOCK) WHERE colum1 = somevalue1 AND column2 = somevalue2 I am trying to understand the execution of above query. As the SELECT is uncommitted, does it start executing before the DELETE is committed? Would this mean that removing the NOLOCK hint would stop the error?

I have a table which I'm only Insert, Delete and Select (no Updates) on Insert, Sometimes it used a session lock (WITH (TABLOCKX)) I have a program using ADO which Select from the table using the RecordSet::Open command to open a simple sequential query. like: Select * from t1 where id >= @from_id And >= @to_id` Or: Select top(10) * from t1 where id >= @id Order by id The id column is an Identity column. When I'm open the query, sometimes I'm getting a deadlock. (the table is locked by the Insert query and the id column's index is locked by the Select) I'm trying to prevent the deadlocks. Should I open the select queries WITH(NOLOCK)? may it prevent the deadlocks?

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM Table1 on the default SQL Server isolation level, the query above will block other queries inserting into Table1, However SELECT Col1, Col2 FROM Table1 WITH (NOLOCK) Will mean other queries are able inserting records. The downside of this is that the query above is susceptible to dirty reads. Is there a way to get the "good" part of no lock behaviour (no locking) but not the "bad" (dirty reads) and if not, why is this?

I am looking for an option to avoid Sch-S locks when running a Select query. I have a database which is controlled by an application written by others as well as my own application. One of the tables has millions of rows. I don't have any issue with dirty reads etc. but I don't my select query to lock the other application indexing or modification queries that have to wait due to Sch-S locks from my query. I tried to set isolation level snapshot before calling my query but that didn't make any difference either WITH NOLOCK option that still acquires an Sch-S lock. I am looking for an option to run a select on a table without acquiring Sch-S or any kind of lock, maybe if we can quickly have snapshot/view or temp copy (will it work as it has millions of rows of data?) of the table without acquiring any kind of lock at all.

For SQL Server 2008 and above, should (NOLOCK) be used on each table of a SELECT statement even if the connection is configured as READONLY?

Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the transaction is either committed or rolled back (as far as I understood from When should you use “with (nolock)” ). At the same time, other RDBMSes, such as PostgreSQL, allow you to read values from a row that has an active writing transaction on it. They would just give you the values that were there prior to the writing. Particularly that seems to be the behaviour with PostgreSQL MVCC (as I have learned from the Introduction to Chapter 13. Concurrency Control ). And I have this burning question. Why is there a possibility for deadlocks in SQL Server while in other RDBMSes you just get the old value before the new one is written? NOTE: I am asking because I may have understood incorrectly.