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
0 votes
0 answers
32 views

Waiting for table level lock innodb

We had a table which in empty. But due to some business logic the table gets queried anyway. This was working fine but today suddenly all the queries on that table were taking a lot of time due to ...
user avatar
0 votes
0 answers
33 views

Writing a ledger with high concurrency

I'm trying to achieve a high level of writes on a single account for a ledger, however, if there is a high level of concurrency, things start to slow down significantly. My current setup is basically ...
user avatar
  • 1
1 vote
1 answer
42 views

single insert ignore on a table with no auto-increment

For this table, in mysql 5.7, CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL, `val` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; The table doesn't have ...
user avatar
  • 113
0 votes
0 answers
11 views

InnoDB Deadlock when transaction waiting for lock already acquired

Transaction (2) holds the following lock: RECORD LOCKS space id 11404 page no 1144152 n bits 72 index PRIMARY of table [tableName] /* Partition [tableName]_p59 */ trx id 28648068046 lock_mode X Record ...
user avatar
0 votes
1 answer
28 views

PostgreSQL triggered update function lock mechanism

I'm fairly new to database administration and PostgreSQL, and there's some things I'm not familiar with, even tho I've read a lot of the PostgreSQL documentation. Here is a simplified version of the ...
user avatar
0 votes
1 answer
33 views

ALTER TABLE … ADD COLUMN on a small table takes forever, pg_stat_activity doesn't show any queries on this table

So this is the query that hangs forever: ALTER TABLE tasks ADD COLUMN in_progress BOOLEAN NOT NULL DEFAULT FALSE; The table tasks has less than 20,000 rows and is queried once every 5 minutes or so. ...
user avatar
  • 105
1 vote
1 answer
34 views

What are the possible downsides or consequences of using ALGORITHM=INPLACE, LOCK=NONE when running alters on a table?

I've been trying to find the answer to this online for a while with no luck. What are possible downsides or consequences to running an alter (such as add index, add column, drop index, drop column) ...
user avatar
0 votes
1 answer
24 views

Can transactions on main table be delayed/deadlock when maintenance transaction on temporal table runs?

I am going to run maintenance procedure similar to described in Cannot delete rows from a temporal history table . The stored procedure will SET SYSTEM_VERSIONING = OFF , delete 3 million rows on ...
user avatar
0 votes
1 answer
27 views

Advice for applying locks in my data processing pipeline

I currently have a Python program that enters rows into a Postgres table that essentially works as a list of data I need to process. These processes create on-disk files and trigger other behavior so ...
user avatar
  • 1
2 votes
2 answers
77 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,375
3 votes
2 answers
61 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
297 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
85 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,375
3 votes
2 answers
393 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
30 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
1 answer
41 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
77 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
238 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
207 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
47 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
106 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
  • 139
1 vote
1 answer
71 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
88 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
146 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
44 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
71 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
25 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
43 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
206 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
49 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
69 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
100 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
602 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
69 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
49 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
73 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
145 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
38 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
76 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
57 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
91 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
98 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
83 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
78 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
150 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
94 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
309 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
97 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

1
2 3 4 5
16