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
2 votes
0 answers
32 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 ...
user avatar
  • 2,223
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 ...
user avatar
5 votes
1 answer
273 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 ...
user avatar
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 ...
user avatar
  • 147
3 votes
2 answers
77 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, ...
user avatar
  • 2,223
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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. ...
user avatar
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 &...
user avatar
  • 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 ...
user avatar
-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. ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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) ...
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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-...
user avatar
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 ...
user avatar
  • 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: ...
user avatar
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 ...
user avatar
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-...
user avatar
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 ...
user avatar
  • 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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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" ...
user avatar
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 ...
user avatar
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 ...
user avatar
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? ...
user avatar
  • 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 ...
user avatar
  • 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? ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 99
2 votes
1 answer
56 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( ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 = '...
user avatar
  • 99

1
2 3 4 5
16