A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.
21
votes
4answers
1k 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 ...
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 ...
15
votes
3answers
4k views
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
15
votes
3answers
2k 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 ...
13
votes
3answers
1k 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, ...
8
votes
2answers
1k 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
489 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 ...
7
votes
2answers
3k 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
...
7
votes
4answers
734 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 ...
7
votes
2answers
333 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
1k 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
310 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
2answers
6k 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
3k 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
4answers
611 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 ...
6
votes
2answers
773 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 ...
6
votes
1answer
163 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
3answers
277 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 ...
6
votes
1answer
434 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 ...
6
votes
1answer
156 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
4answers
384 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 ...
5
votes
3answers
1k 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 ...
5
votes
2answers
239 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
4answers
497 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 ...
5
votes
2answers
665 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
151 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
1answer
683 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:
...
5
votes
2answers
353 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
362 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
4answers
1k 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 ...
4
votes
2answers
101 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 ...
4
votes
2answers
155 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
5k 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
121 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 ...
3
votes
3answers
103 views
Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million).
We're ...
3
votes
1answer
278 views
How to force the use of row locks?
I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT
alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE)
go
...
3
votes
1answer
188 views
Does blocking always mean open transaction?
This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
3
votes
2answers
440 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 ...
3
votes
2answers
2k 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 ...
3
votes
2answers
216 views
Writing transactions to .MYD file to take Backup
I'm trying to backup MySQL DB by archiving the data directory of required databases.
I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
3
votes
1answer
534 views
No “Using locked pages for buffer pool” in SQL Server log
As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to ...
3
votes
2answers
574 views
MySQL Locks while CREATE TABLE AS SELECT
I am running the following (dummy) query
CREATE TABLE large_temp_table AS
SELECT a.*, b.*, c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.bar = c.bar
Suppose the query ...
3
votes
2answers
1k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
1answer
396 views
Using (NOLOCK) in a READ COMMITTED transaction block
We're using SQL Server 2000.
I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN ...
3
votes
1answer
135 views
SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]
I have the following table structure (simplified, pseudocode):
TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)
TABLE dbo.Transfers (
Id int NOT NULL IDENTITY PRIMARY KEY,
...
3
votes
2answers
126 views
pessimistic locking and client death
We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
3
votes
1answer
270 views
Locking order in cursors (Oracle)
I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ?
For example,
DECLARE
CURSOR test_cursor IS
SELECT field1, field2
FROM table1
ORDER ...
3
votes
1answer
1k 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 ...
3
votes
2answers
543 views
Why did my UPDATE statement not complete?
Good morning. Running SQL 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the ...
3
votes
1answer
770 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 ...
