A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.

learn more… | top users | synonyms (3)

28
votes
4answers
3k views

Is NOLOCK always bad?

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 ...
27
votes
4answers
18k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
24
votes
1answer
8k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
21
votes
3answers
12k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
21
votes
3answers
7k views

Managing concurrency when using SELECT-UPDATE pattern

Let's say you have the following code (please ignore that it's awful): BEGIN TRAN; DECLARE @id int SELECT @id = id + 1 FROM TableA; UPDATE TableA SET id = @id; --TableA must have only one row, ...
20
votes
3answers
4k views

Justify NOT using (nolock) hint in every query

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 ...
19
votes
4answers
11k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
18
votes
6answers
486 views

Can I rely on reading SQL Server Identity values in order?

TL;DR: The question below boils down to: 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 ...
18
votes
5answers
2k views

In Sql Server, is there a way to check if a selected group of rows are locked or not?

We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large ...
15
votes
2answers
681 views

Lock CREATE TABLE

In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this: IF NOT EXISTS (SELECT * FROM sys....
14
votes
5answers
503 views

What is the exact relationship between a database transaction and locking?

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response. As a long-time application developer, I know at some level what a transaction is (I use ...
13
votes
3answers
2k views

Will increased network latency cause table locks in MS SQL Server?

If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that ...
13
votes
2answers
12k views

What is blocking and how does it happen?

I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
13
votes
1answer
210 views

Massive INSERTs blocking SELECTs

I have a problem with a massive amount of INSERTs that are blocking my SELECT operations. Schema I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, [...
12
votes
1answer
46k views

Detecting the locked table or row in SQL Server

I'm trying to understand/learn how to track down the details of a blocked session. So I created the following setup: create table foo (id integer not null primary key, some_data varchar(20)); ...
12
votes
2answers
10k views

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? ...
11
votes
3answers
13k views

Tracing, debugging and fixing Row Lock Contentions

Off late, I've been facing a lot of row lock contentions. The table in contention seems to be a particular table. This is generally what happens - Developer 1 starts a transaction from Oracle ...
11
votes
1answer
1k views

Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?

We recently migrated our production instances from SQL 2008 R2 to brand new SQL 2014 servers. Here is an interesting scenario that we uncovered with our usage of Service Broker. Consider a database ...
10
votes
2answers
628 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
10
votes
1answer
8k views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
10
votes
2answers
1k views

SQL Server Index Update Deadlock

I have 2 queries which when run at the same time are causing a deadlock. Query 1 - update a column which is included in an index (index1): update table1 set column1 = value1 where id = @Id Takes X-...
10
votes
1answer
21k views

Could not continue scan with NOLOCK due to data movement

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 ...
10
votes
2answers
750 views

How does SQL Server determine the order it takes locks in while selecting a table?

I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc ...
9
votes
3answers
3k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
9
votes
3answers
3k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
9
votes
1answer
149 views

Blocked sessions waiting with PAGELATCH_* wait types?

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types? I previously assumed that SQL server would only report blocking sessions in the ...
8
votes
2answers
14k views

Difference Between Row Level and Page Level Locking and Consequences

When attempting to run my Maintenance Plan, I receive the following error: Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized ...
8
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) ...
8
votes
2answers
15k views

When a record is locked in Oracle, can we know which record is locked?

When a record is locked ,can we know which one is locked? How can I get the record rowid or something else info? I can get some info by this sql SELECT c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c....
8
votes
3answers
27k views

InnoDB row locking - how to implement

I've been looking around now, reading the mysql site and I still can't see exactly how it works. I want to select and row lock the result for writing, write the change and release the lock. ...
8
votes
2answers
2k views

Options for setting NOLOCK hint in dataset queries

Some context: At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by ...
8
votes
2answers
8k views

In SQL Server, how do read locks work?

Suppose I have the following long-running query UPDATE [Table1] SET [Col1] = 'some value' WHERE [Col2] -- some clause which selects thousands of rows and suppose the following query is executed ...
8
votes
4answers
4k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
8
votes
1answer
8k views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
7
votes
3answers
4k views

SQL Server - what isolation level for non-blocking select statements?

I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
7
votes
4answers
5k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
7
votes
4answers
2k views

SQL Server: what should be done when a process is blocking another process?

I'm an accidental DBA and still learning. When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done? Should I simply kill those processes or is ...
7
votes
1answer
7k views

How should I interpret the “lock time” within mysql slow query log?

I'm trying to understand how to best interpret the lock time for the queries which show up on our MySQL slow query log. For instance if an UPDATE query has 10 second lock time. I presume it is the ...
7
votes
2answers
9k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
7
votes
2answers
5k views

Updating a table with more than 850 million rows of data

I have been tasked with writing an update query to update a table with more than 850 million rows of data. Here are the table structures: Source Tables : CREATE TABLE [dbo].[SourceTable1]( [...
7
votes
2answers
38k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
7
votes
1answer
922 views

Deadlock graph with a lock on an index on a seemingly unrelated table

I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE. This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a ...
7
votes
1answer
1k views

Performance impact of sp_configure blocked process threshold change

I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use EXECUTE sp_configure 'blocked process threshold', 5 In combination with a server ...
7
votes
3answers
2k views

Postgres, MVCC, and Locking

I have a series of SQL statements that look like the following: BEGIN; SELECT counter FROM table WHERE id=X FOR UPDATE; REALLY COMPLEX QUERY; UPDATE table SET counter=Y WHERE id=X; END; I'd like to ...
7
votes
1answer
224 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
6
votes
2answers
22k views

SQL Server Transaction Timeout

Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
6
votes
4answers
601 views

Resources for understanding SQL Server locking and concurrency?

As demonstrated by a recent question of mine locking and concurrency are HARD. Can you suggest any good resources for intermediate-to-advanced SQL professionals to do a thorough study on these that ...
6
votes
5answers
8k views

Can I be automatically notified of prolonged blocking in SQL server?

About once a week I have to resolve a blocking chain on a SQL Server 2005 database, caused by a long-lived read lock from an Access 2003 front-end. The lock is taken out whenever a user opens a ...
6
votes
1answer
2k views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
6
votes
1answer
195 views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...