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.
789
questions
2
votes
0
answers
30
views
How to get the specific queries that are contributing to high lock wait times?
I am looking at SQL lock wait time perf mon counter and it is indicating high value ranging from 5 to 20 seconds during certain times of the day.
I have setup extended events which gives me the T-SQL ...
- 2,205
3
votes
2
answers
34
views
Inconsistent data when combining (LEFT) JOIN with SELECT FOR UPDATE
I have recently stumbled upon a seemingly odd behavior of SELECT ... FOR UPDATE when combined with (LEFT) JOIN. Here is the table structure as well as a scenario to reproduce the result:
Table ...
5
votes
1
answer
272
views
Minimise duration of table lock
I've got a SQL table that needs to be updated daily.
There may or may not be queries against that table while the update is happening. It's around 500,000 rows.
We have an issue where there is locking ...
- 53
0
votes
0
answers
24
views
Know which specific rows are locked in a KEY X lock? [duplicate]
I'm investigating a lock issue on a query I have and I already can see some KEY locks. By joining with sys.partitions and sys.indexes I know what's the index it's doing the lock but what I wanted now ...
- 147
2
votes
2
answers
70
views
Does selecting data from heap (table without clustered index) with nolock block writers?
I tried replicating the scenario given here: https://www.sqlskills.com/blogs/paul/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan/
Created 2 tables - table1 with clustered index, ...
- 2,205
3
votes
2
answers
316
views
In SQL Server / Azure SQL is there a way to see the locks a query produced or wants to produce?
I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on ...
- 147
3
votes
1
answer
24
views
MySQL replication setup based on an LVM snapshot – am I forgetting anything?
I'm tasked with starting a brand new replica of a client's MySQL 5.7.35 database server. While I've observed others doing this in the past in various circumstances, I haven't been in charge of this ...
- 135
1
vote
0
answers
34
views
Resolve Orphan Transaction locking
My SQL Server version is Microsoft SQL Server 2019 (RTM) and I faced a new locking problem.
An SSIS package was running. After a while it became suspended by a session with ID of (-2).
I know this ...
- 149
0
votes
2
answers
67
views
SQL Server - Exclusive lock taken by a SELECT statement - Why?
I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below.
I'm confused because the deadlock is ...
- 331
3
votes
1
answer
208
views
What locks are held during an update
When I have a transaction with multiple queries, I am able to test which locks are held between queries by using waitfor and checking the dm_tran_locks.
But I cannot "pause" one query in ...
- 403
2
votes
2
answers
168
views
Writing a query that will quit if there's a lock on a target row
Is it possible to write an UPDATE query that will simply quit if the record it tries to change is locked by another process (rather than waiting for the lock to be released)?
I have a process that ...
- 811
0
votes
0
answers
21
views
Postgres - expected behaviour of command timeout when faced with a locked record
I have a connection to a postgres database with a command timeout of 3 seconds. I'm using this connection to update records in a table, and occasionally those records are locked by another session.
...
- 811
1
vote
1
answer
62
views
How to avoid deadlocking in a stored procedure with SERIALIZABLE isolation level, INSERTs, UPDATEs, and DELETEs
I'm having trouble with deadlocks in a stored procedure.
This stored procedure is part of a custom full-text-like search system. It's called after a record is updated, and inserts/updates a list of &...
- 129
1
vote
1
answer
63
views
Deadlock on common IX lock
I am trying to make sense of a deadlock. The table in question mytb is only used in the following query:
delete mytb with (uplock,holdlock) where key_value=@value
called through sp_executesql. Both ...
- 403
-1
votes
1
answer
41
views
How to integrate Postgres advisory lock functions into SELECT and UPDATE?
Using Postgres 13 in a Node.js application, I have a simple task to do:
SELECT a row (identified by its primary key)
Run some application code that cannot be done in the database* with that data.
...
- 289
3
votes
3
answers
73
views
Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?
In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
- 289
0
votes
1
answer
40
views
Best way to run performance tests in a production PostgreSQL 13 database?
First, I'm asking this question because I'm studying to be a DBA. Recently I learned a lot about database locking, and then some questions arouse in my mind.
Suppose I'm a DBA, I'm in charge of a huge ...
- 126
-1
votes
1
answer
63
views
Deadlock and locks on multitenant database
I work for a saas company that uses multi tenancy by a tenant_id, however we perform nightly jobs that dump mass amount of data from the last day of each tenant, sometimes we face deadlocks or simply ...
- 1
0
votes
0
answers
21
views
Update Row --- get rid of row lock for perfomance? (postgres)
I need to update table A with the earliest "event" from Table B (event_time), matching on an ID.
A might have 1000 ids ... and B has a bunch of foreign ids and linked events.
So each day, a ...
- 1,493
0
votes
0
answers
35
views
Azure SQL truncate table schema locking
Related to this question but about Azure SQL and causing other issues.
We have data factory pipelines that replace all data in certain tables. The pipelines can take several minute to run and to ...
- 378
5
votes
1
answer
194
views
Query that returns no records hangs on lock
We noticed this weird situation, that a query that returns no records, will wait for a lock when WITH(UPDLOCK) is specified.
The query is as simple as
SELECT primaryKey FROM someTable WITH(UPDLOCK)
...
- 153
-1
votes
1
answer
40
views
Best method for isolating long running locks?
On a SaaS production database with 7 users (5 human, 2 backend) many are experiencing SQL cliënt timeouts at 30 seconds, our standard .Net CommandTimeout.
This SQL Server instance (out of our ten) ...
- 59
1
vote
2
answers
62
views
Locking and blocking lifetime on UNION queries on MS SQL Server
Can anyone shed any light on how locking works on UNION queries? Say I have the below query and the Archive tables take 1 min to run while the non-archive tables take 5s. Are any locks taken on the ...
- 213
0
votes
2
answers
95
views
Massive DROP on a production database with the shortest downtime
I was redesigning legacy database and have merged lots of tables into just four and now I'm looking for a way to drop a several thousand obsolete tables as quickly as it possible.
As far as I know we ...
- 111
3
votes
2
answers
540
views
Why does upsert result in a deadlock?
I'm doing the below upsert statement, which I need to have run multiple times in a short timeframe. I wrote this statement based on examples from Aaron Bertrand's Please stop using this UPSERT anti-...
- 133
0
votes
3
answers
65
views
Temporary database write-lock without dropping connections
We have prepared a piece of software that creates a clone of our production database (SQL Server) that contains only the most recent data. We would like to allow developers to instantly create a ...
- 51
0
votes
0
answers
43
views
If a schedule is allowed by 2 phase locking protocol, does that mean all possible interleaved schedules (same transactions) are serializable?
I came across following results on web:
1)2PL ensures conflict serializability(cs) and hence avoid non serializable schedules.
2) But not every cs schedule allowed by 2pl.
3)this is a theorem i found: ...
2
votes
0
answers
57
views
When/how does removing an index cause a table to lock in MySQL?
Dropping an index in MySQL 8 is INPLACE, not INSTANT, per https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html.
As I understand it, INPLACE means that there is potentially some ...
- 141
0
votes
0
answers
116
views
MariaDB "freezes" during mariabackup operation
The Scenario:
I have a MariaDB running in a server named dbmaster, in a dedicated ProxMox server. This dbmaster runs:
Ubuntu 20.04
MariaDB 10.3.31 (installed from repository)
maria-backup 1:10.3.31-...
2
votes
0
answers
37
views
Extended Event Failing To Capture Intent Locks
I have a SQL server with many unused databases and followed the linked article to set up an extended event to track intent locks and identify which DB's are actively being used, and which aren't and ...
- 143
2
votes
1
answer
51
views
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
What locks does a REVOKE ALL PRIVILEGES ON a PostgreSQL table require?
Specifically, could it get blocked by a long running ALTER TABLE which adds a column to the table?
0
votes
1
answer
37
views
Taking many row level locks on a rows with little contention - how does that affect PostgreSQL scalability (and RDMS in general)
I use transactions to lock on rows in my database. Generally, the number of 'things' accessing the same rows at a single time is small, however, for correctness I would still like to use transactions ...
- 101
-1
votes
2
answers
83
views
MariaDB seems to be locking all the tables
I am running MariaDB locally on
windows 10. XAMPP version 3.2.4 and MariaDB Version 10.4.7
I am moving MYISAM tables to INNODB and trying to prove that the locking issues we seem to be having will be ...
- 1
0
votes
1
answer
81
views
resolve lock wait timeout delete innodb table
In an application I work with, DELETEs are rare, though they seldom happen. When they do, they remove few rows but from several related tables, like 8 to 10.
Most of the times, mainly when the ...
- 376
0
votes
1
answer
73
views
What replacement algorithm does this ALTER TABLE use
Question
I am trying to figure out what kind of replacement algorithm a command like the following would use
ALTER TABLE `catalog_category_product_index_store1` COMMENT='Catalog Category Product Index ...
- 101
0
votes
1
answer
47
views
Create table with foreign keys blocked by other Table-level Lock
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.
When creating a new table B with foreign keys to table A, the operation tries to acquires ...
0
votes
2
answers
123
views
Postgres deadlock on sharedlock
I'm having a problem where a couple UPDATE sql statements are locking up on a particular table in a database. For some reason these statements seem to be requiring access to a "ShareLock" ...
0
votes
0
answers
35
views
mysql synchonizing inserts and updates with transactions
I have two tables, call them transaction and master. The master has a primary key which is also a foreign key in the transaction table. If a transaction comes in with a key that's not in the master ...
0
votes
1
answer
90
views
Table Lock Escalation - Performance Questions
I have few questions on table lock escalations, please confirm or disprove:
Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?
When Shared lock is ...
- 4,408
3
votes
1
answer
273
views
MySQL/MariaDB: can a SELECT query block an INSERT query?
Let's suppose that I have a query in the form:
INSERT INTO tableA (...) select ... from tableB;
Now, is it possible that this query, while executing, blocks plain INSERT statements to tableB? ...
- 654
1
vote
1
answer
87
views
Oracle multithreaded JDBC bulk select high-performance data export
The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
- 11
7
votes
3
answers
1k
views
How to know which application or user put the SQL Server Database in single user mode
In my case, there is SQL Server Database which is used by multiple teams. The database has suddenly gone into Single User mode. Now, how to identify the responsible user or application who did that? ...
1
vote
1
answer
269
views
How to get SQL statement in Db2 database for application that is holding lock on table?
I am trying to get SQL statement that is holding the lock on table.
Sample:
Create table: create table admin.tab (id int not null primary key, col1 int);
Insert data: insert into admin.tab values (1, ...
- 77
-1
votes
2
answers
42
views
MariaDB (InnoDB) client disconnect release lock timeout
I have MariaDB (InnoDB). my client is setting lock on table.
In case he would fail for some random reason, what timeout setting I can set on the server, to release locks?
I know that DB discovers that ...
- 99
2
votes
1
answer
55
views
Are foreign key share locks taken out atomically?
I'm trying to understand how a deadlock happened in our database and I'm wondering if the culprit could have been an update that did not run in a transaction.
If I have two tables
CREATE TABLE user(
...
- 459
0
votes
0
answers
26
views
Large number of row locks on a partitioned slave table
I am testing mysql5.7.28 partition. Table in master is not partioned. The table in slave is partitioned. When I start the replication, slave lag keeps on increasing.
This is the table schema in master
...
- 89
0
votes
1
answer
36
views
SELECT from table without blocking INSERTs into it?
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 ...
- 141
1
vote
2
answers
445
views
How to lock table in SQL Server to prevent other transactions from INSERT and avoid deadlocking?
I'm writing a database trigger on Table A. In said trigger, I insert a new record into Table B by doing:
Look up the PK ID of Table B
Increment the PK ID and insert new row into Table B
(Note: Table ...
- 243
3
votes
2
answers
573
views
How to use application lock in SQL server from two databases
How to use a lock from two different databases. When a session acquires the lock, there is nothing preventing different session to acquire the same lock from different database.
First session:
USE ...
- 304
-1
votes
1
answer
60
views
Locking row for INSERT, still getting duplicate key exception
I am trying to run the following query:
WITH upd AS (
UPDATE fila_all SET id = id WHERE
id = (select id from fila_all fa where fa.id not in (select msg_id from fila_consumers where group_name = '...
- 99