Skip to main content

Questions tagged [locking]

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

Filter by
Sorted by
Tagged with
7 votes
4 answers
8k 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 ...
Rick James's user avatar
  • 1,271
4 votes
1 answer
9k views

DBA Blocking Query Email Alert

Does anyone have a good Sql Alert which sends email notifications, when query Blocking has occurred over many minutes? I know how to write my own, however seems to be a good code library on internet. ...
user avatar
31 votes
4 answers
33k 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 ...
sh-beta's user avatar
  • 609
19 votes
2 answers
23k 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? ...
ErikE's user avatar
  • 4,315
23 votes
3 answers
5k 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 ...
datagod's user avatar
  • 7,101
14 votes
2 answers
50k 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 ...
David Gray Wright's user avatar
8 votes
1 answer
24k 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 ...
dangelsaurus's user avatar
3 votes
1 answer
4k views

LOCK IN SHARE MODE

On dev mysql LOCK IN SHARE MODE is described as... SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
user avatar
18 votes
2 answers
21k views

How to find the query that is still holding a lock?

Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row. For each lock acquired, is there any way to determine which SQL statement (...
tanitelle's user avatar
  • 437
13 votes
4 answers
8k 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 ...
vitalidze's user avatar
  • 233
12 votes
5 answers
31k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
Pacerier's user avatar
  • 491
11 votes
2 answers
3k 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) ...
dsum's user avatar
  • 729
11 votes
2 answers
5k views

Strategies for "checking out" records for processing

I'm not sure if there is a named pattern for this, or if there isn't because it's a terrible idea. But I need my service to operate in an active/active load balanced environment. This is the ...
Dean's user avatar
  • 341
68 votes
1 answer
34k 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 ...
Ben's user avatar
  • 877
30 votes
6 answers
6k 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 ...
Fabian Schmied's user avatar
21 votes
2 answers
27k 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?
jrara's user avatar
  • 5,343
9 votes
3 answers
8k 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 ...
growse's user avatar
  • 375
9 votes
1 answer
2k views

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI. How can I be seeing ...
grassbl8d's user avatar
  • 467
9 votes
2 answers
25k views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
Marcello Miorelli's user avatar
6 votes
1 answer
8k views

Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?

Following on the heels of this question does changing the length limit (type-modifier) of varchar() result in a table rewrite or a lock that takes more time than changing the CHECK constraint? From ...
Evan Carroll's user avatar
  • 63.7k
5 votes
1 answer
1k views

During rollback, is lock escalation reversed

Generally, SQL Server will take locks during updates. It also supports Lock Escalation: Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing ...
Michael Green's user avatar
4 votes
1 answer
8k views

MySQL consistent nonlocking reads vs. INSERT ... SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
Luis Casillas's user avatar
3 votes
1 answer
610 views

Request Sch-M lock on a table

I have access to a database in read uncommitted mode and need to do maintenance on a table in an operative environment. I need to acquire an exclusive lock on a table, do a job, then release the lock. ...
arthur's user avatar
  • 888
3 votes
2 answers
8k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
quanta's user avatar
  • 1,006
61 votes
4 answers
60k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
user avatar
34 votes
4 answers
5k 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 ...
DataGirl's user avatar
  • 465
28 votes
3 answers
16k 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, ...
ErikE's user avatar
  • 4,315
19 votes
2 answers
53k 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 ...
cm007's user avatar
  • 363
16 votes
1 answer
22k views

Why does UPDLOCK cause SELECTs to hang (lock)?

I've got a select in SQL SERVER that locks the whole table. Here's the setup script (make sure you don't overwrite anything) USE [master] GO IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = '...
marius-O's user avatar
  • 263
16 votes
2 answers
25k 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 ...
BestPractices's user avatar
15 votes
2 answers
8k views

How can I set a column to NOT NULL without locking the table during a table scan?

(former question was: Will Postgres use a multi-column index when setting multiple colums not null?) Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
John Bachir's user avatar
13 votes
2 answers
23k 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 ...
Onion-Knight's user avatar
  • 1,099
11 votes
1 answer
15k views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
Aleksei Averchenko's user avatar
11 votes
2 answers
10k 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 ...
clops's user avatar
  • 337
9 votes
3 answers
6k 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 ...
melaos's user avatar
  • 193
9 votes
2 answers
4k views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
user2732180's user avatar
9 votes
5 answers
16k 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 ...
Warrior Bob's user avatar
7 votes
1 answer
7k views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they happen?...
Toolman21's user avatar
  • 127
7 votes
4 answers
4k 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 ...
jrara's user avatar
  • 5,343
6 votes
1 answer
3k views

How to atomically replace a subset of table data

In PostgreSQL 9.6 I have a table T like this category | id | data ---------+----+------ A | 1 | foo A | 2 | bar A | 3 | baz B | 4 | eh B | 5 | ...
vektor's user avatar
  • 401
6 votes
2 answers
8k views

Using sp_getapplock to implement a queue. Is it correct? Is there a better way?

I've been reading a series of posts by Paul White about SQL Server Isolation Levels and came across a phrase: To emphasise the point, pseudo-constraints written in T-SQL have to perform ...
Vladimir Baranov's user avatar
6 votes
1 answer
12k 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 ...
grassbl8d's user avatar
  • 467
5 votes
1 answer
9k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
davidjhp's user avatar
  • 429
5 votes
2 answers
2k views

Alternative to CLUSTER without table lock

I'm facing performance degradation and increasing storage usage as a result of frequent new and updated records causing index and storage fragmentation. VACUUM doesn't help much. Unfortunetely ...
brauliobo's user avatar
  • 176
5 votes
2 answers
392 views

Transaction with lock on a specific table

NOTE : If there is similar question, please refer me to it. I have two tables Request & Receipt. I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. ...
ABS's user avatar
  • 157
4 votes
2 answers
264 views

Sample query/DDL for producing page locks on a clustered index in SQL Server (without hints)

We currently have some indexes where ALLOW_PAGELOCKS is set to off. This presumably was done in order to reduce deadlocks. However I doubt that it would really had an effect back then. Now I am ...
Martin Guth's user avatar
4 votes
1 answer
969 views

Looking up which record(s) correspond to a specific resource description in dm_tran_locks

I've spent the better part of two days trying to figure out what exactly is being locked in one of my tables and why I'm getting deadlocks that seem to make no sense at face value. I've found ...
Mirrana's user avatar
  • 224
4 votes
1 answer
121 views

Maximizing throughput on bulk data processing

I'm using SQL Serverversion 14.0.3035.2 I have a pile of information that I need to process from table A and insert the modified data into table B. I have written an application to query the data, do ...
Zakk Diaz's user avatar
  • 143
3 votes
2 answers
8k views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
Mara's user avatar
  • 267
2 votes
1 answer
4k views

MySQL MyISAM Locking question

It was my understanding that MyISAM uses table locking during inserts and updates. To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
ryy's user avatar
  • 123