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 (1)

22
votes
4answers
2k 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 ...
18
votes
3answers
7k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
17
votes
3answers
3k 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 ...
16
votes
3answers
2k 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 ...
16
votes
3answers
2k 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, ...
14
votes
2answers
488 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 ...
11
votes
5answers
169 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 ...
10
votes
4answers
2k 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 ...
8
votes
2answers
5k 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
3k 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?
8
votes
3answers
863 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 ...
8
votes
3answers
972 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 ...
8
votes
1answer
162 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 ...
8
votes
2answers
424 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 ...
7
votes
4answers
982 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
2k 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 ...
7
votes
2answers
1k 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
3k 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? ...
7
votes
2answers
9k 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 ...
6
votes
4answers
430 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
4answers
4k 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
3answers
2k 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 ...
6
votes
1answer
2k 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 ...
6
votes
4answers
1k 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 ...
6
votes
1answer
1k views

What factors contribute to lock-percentage with MongoDB

We're attempting to better optimize how we're using our MongoDB instance. We seem to be routinely getting high lock-percentages, and are looking to help minimize that. Here is some mongostat output: ...
6
votes
1answer
416 views

UNIQUE index key violation

I have a table with a PK and a unique non-clustered index, as follows: CREATE TABLE Table1 ( Id INT IDENTITY(1,1) NOT NULL, Field1 VARCHAR(25) NOT NULL, Field2 VARCHAR(25) NULL, ...
5
votes
2answers
1k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) What is ...
5
votes
4answers
2k 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 ...
5
votes
2answers
620 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 ...
5
votes
2answers
144 views

DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
5
votes
2answers
3k 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 ...
5
votes
2answers
197 views

Summary of locks for a query

Does SQL Server have any simple way to fetch metrics on how many (and possibly what type) of locks a given query has taken out during the course of it's execution? Rationale... I'm in the process of ...
5
votes
2answers
583 views

SQL Server - RangeX-X and RangeI-N locks

I came to a dead point in a deadlock analyze. According to msdn: RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range. RangeI-N are Insert range, null resource ...
5
votes
1answer
671 views

Global locking for multi-master Oracle GoldenGate replication

This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se. Problem I'm working on an intercontinental data replication ...
4
votes
2answers
7k 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 ...
4
votes
3answers
9k views

Set “lock wait timeout” in MySQL

Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
4
votes
1answer
186 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 ...
4
votes
2answers
604 views

Will creating partitions reduce locking and how do we implement this in sql-server?

A client wants our application to process more data faster so arranged a meeting with their dba to discuss options. This application generates quite a lot of data that is used for reporting. Before ...
4
votes
2answers
204 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 ...
4
votes
2answers
3k views

What is the best practice for storing image varbinary(max) data in a database table?

I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
4
votes
1answer
211 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...
4
votes
1answer
152 views

Can I tell SQL Server to “do something” when a connection is closed?

Can I tell SQL Server to "do something" (e.g. release an application-level lock) when the connection is closed for whatever reason? Background: I want to lock a record on the application level, not ...
4
votes
2answers
245 views

Schema blocking in READ_COMMITTED_SNAPSHOT

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues. Background: Assume an online ordering system (ecommerce). ...
4
votes
2answers
13k 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 ...
4
votes
1answer
262 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 ...
4
votes
1answer
148 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
4
votes
2answers
1k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
4
votes
1answer
2k views

MySQL get next unique value without auto increment

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100. WorkorderID ...
4
votes
1answer
2k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...