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
1answer
33 views

Does CREATE TABLE AS lock the table?

In Postgres, does this query lock films for reads and/or writes? CREATE TABLE films2 AS TABLE films;
1
vote
1answer
26 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 ...
1
vote
1answer
66 views

How can I lock rows FOR UPDATE without returning data over the connection?

I want to do something like this: begin; select * from foos where owner_id=123 and unread=true order by id for update; update foos set unread=false where owner_id=123 and unread=true; commit; The ...
0
votes
1answer
24 views

How to get the SQL_ID that is blocking another SQL?

Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL. Here is my script, if you try this, You never will get ...
0
votes
1answer
21 views

MySQL Lock Wait Time Out Exceeded upon Delete

I have a Delete Statement which fails with Below Exception com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction It waits for 50 ...
6
votes
1answer
288 views

SQL Server - Is there a way to prevent shared range locks to 'infinity' when using an indexed view?

I have your basic header/details table (think orders and order details). The header table has an identity column as the clustered key, the detail has the header id and a line number column as the ...
2
votes
1answer
33 views

Blocked Process Report - which process does lockmode apply to?

I am analyzing a blocked process report and my blocking-process XML is as follows (some sections redacted for simplicity) <blocking-process> <process status="running" spid="...
0
votes
0answers
8 views

Multiple Writes to Same table in Redshift

I have a table in Redshift. My Application has Multiple Writes/Reads on this Table concurrently. How I do Make sure Reads won't wait for Writes to Commit/RollBack. I wanted to know which Lock Mode I ...
0
votes
1answer
33 views

How to create a clustered index on a schema bound view with the least amount of contention caused?

I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 ...
6
votes
1answer
196 views

Sometimes the updated rows are not locked within instead of update trigger

Here is as small repro: create table dbo.t (id int primary key, v int); insert into dbo.t values (1, 1), (2, 2); create table dbo.s (id int primary key, v int); insert into dbo.s values (1, 10); go ...
0
votes
2answers
55 views

Sql Server Standard 2014: Reindex job locked by other transactions

On a customer of ours we have Microsoft SQL Server 2014 - 12.0.4100.1 (X64) installed. It's a heavy transactions schema (especially during nights) and we have a scheduled REINDEX maintenance job that ...
0
votes
1answer
22 views

Explicit locks in RedShift

I'm new to AWS Redshift and I wanted to know How i can implement my use-case. I have a Redshift table which gets lot of Inserts/Selects. In some cases I need to delete some data from that table. I ...
1
vote
1answer
41 views

Can I avoid TABLOCKX on DELETE?

Goal: Delete several billion records old records (roughly 600GB) across three tables with zero downtime. Methodology and Background: I plan to delete batches of records associated with 1 Million ...
2
votes
2answers
51 views

What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?

In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query? BEGIN TRANSACTION T1 SELECT Field1, Field2, Field3 INTO TableB FROM ...
0
votes
0answers
6 views

Release_Lock return 0 even when the session has it

I have a strange issue in my application. I develop an application that uses a server lock in a MySQL database. I obtained the lock in the database but when I am going to release the lock, sometimes ...
1
vote
2answers
38 views

For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while ...
0
votes
0answers
15 views

MySQL, DROP TABLE generates a thread locking itself?

I'm trying to drop a table on a local mysql instance, and the DROP statement (DROP TABLE wqs_info) hangs out forever... Looking at mysql logs, I see the lock dumps: --Thread 18692 has waited at ...
1
vote
1answer
40 views

Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them? If so, what happens when SQL Server ties to write ...
0
votes
1answer
38 views

Do table locks get released after a query finishes when WAITFOR is used after that query within the same batch and transaction?

If I have a query that reads from a table, if that query is proceeded with the WAITFOR keyword (within the same batch and transaction) will the table lock of the preceding query be held until the ...
0
votes
0answers
21 views

Long Running Transactions with Active Status

I could see long running transactions in my Innodb Status, I'm using RDS Aurora Mysql 5.6 Version. ---TRANSACTION 977075315, ACTIVE 70113 sec 2 lock struct(s), heap size 376, 0 row lock(s), undo log ...
2
votes
1answer
151 views

Differences of latch and lock

Recently when i was trying to find information about latch_ex wait type, i come across one blog as below which stated about latch and lock. After reading this blog , i m just curious in one thing. ...
0
votes
1answer
148 views

“Lock wait timeout exceeded; try restarting transaction” for my delete query

I'm using RDS Aurora with MySQL 5.6. I have a delete query on a table which runs once in a day and it fails with lock wait time outexception, try restarting transaction. This issue is not happening ...
0
votes
1answer
28 views

why innodb generate table-lock when two sql using different index?

I know innodb-engine row lock by add lock to index item. But I don't understand the following scene. Prepare sql: DROP TABLE IF EXISTS `tbl_order`; CREATE TABLE tbl_order ( `order_id` BIGINT NOT ...
9
votes
1answer
303 views

Key-range RangeI-N lock compatibility in SQL Server

The documentation states that the RangeI-N lock (Insert range, null resource lock; used to test ranges before inserting a new key into an index) is compatible with itself (see the compatibility matrix)...
4
votes
1answer
74 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 ...
0
votes
1answer
126 views

Query hanging in ClientRead and blocking all others

This is related to Debugging a hanging session/lock Every so often (~1-2 times a month) we have our database lock up because there is one query that doesnt finish, and that has requested locks that ...
1
vote
0answers
86 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
0
votes
2answers
40 views

Can a SELECT statement block an ALTER TABLE?

I see in Postgres docs: Two transactions cannot hold locks of conflicting modes on the same table at the same time During maintenance (schema migration) we saw cases when we can't ALTER TABLE ...
0
votes
1answer
28 views

how mongo db does locking for updates/inserts?

I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them: 1. keep all user'...
0
votes
0answers
24 views

ALTER TABLE causing transaction aborted

There are two transactions. One transaction I used BEGIN RO for read-only transaction. While the other one I used BEGIN to allow me adding a new column to my table called dummy_table. This is what I'...
5
votes
1answer
130 views

Deadlock Graph - Surviving Statement inputbuf does not show the object that was locked

I have captured a deadlock on my system and the (anonymised) XML output is as follows: <deadlock> <victim-list> <victimProcess id="processf4d9233468" /> </victim-...
9
votes
1answer
774 views

Does UPDATE without WHERE clause lock a table in PostgreSQL?

Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted? E.g. if I run UPDATE t1 SET key = 'value' Can I ...
1
vote
1answer
104 views

What is the performance cost of enabling “log_lock_waits” on Postgres?

I am trying to understand what is the performance cost of enabling log_lock_waits on Postgres. As per this documentation, while log_lock_waits = on: Controls whether a log message is produced when ...
2
votes
1answer
101 views

What is an Intent Lock in SQL Server

I have this query SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE c SET c.Score = 2147483647 FROM dbo.Comments AS c WHERE c.Id BETWEEN 1 AND 5000; Which have these stats +----------...
0
votes
0answers
43 views

How do I stop Amazon Web Service's RDS from locking out connections during backups?

I've been using RDS for years, and this only became an issue a few months ago. I was pulling my hair out trying to find the source of the issue... only to figure out it's suddenly RDS. I've made no ...
4
votes
0answers
123 views

How can I reduce time spent acquiring the mongodb schema lock?

We have a mongodb 4.0.10 cluster, backed by WiredTiger, in production, with a 3 node replica set consisting of a master and two slaves. One of the slaves has another service co-located that queries ...
0
votes
2answers
37 views

How can I force a minimum isolation level in a procedure?

How can I force a minimum isolation level in a procedure in mssql? I can use "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" inside my procedure to set the transaction-level. But this command will ...
2
votes
3answers
204 views

How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries

*Edit: Solution turned out to be nothing in the answers here, I was using SQLAlchemy in Python, which is an ORM. I was executing the delete statement below in a transaction, but never committing it. ...
0
votes
1answer
45 views

SQL transaction: Delete between two selects possible?

I have two SELECT statements within a transaction (repeatable read) Select @firstItem =id from myTable Where .... --Do some more magic, so I can't concat the the two queries!!!! and Select * from ...
0
votes
0answers
103 views

SQL Server : Memory pressure,Spiked user connections and CPU usage

I have the below sql server environment. SQL Server 2016 standard edition. 128 GB RAM 96 GB alllocated to SQL Server 8 tempdb's with 1 GB each. Have a major database with 2.5 TB size. Reporting also ...
4
votes
2answers
158 views

Ms sql MERGE INTO locks whole table for updates

I have a table for statistic values, it holds millions of records, which is defined like this: CREATE TABLE [dbo].[Statistic] ( [Id] [INT] IDENTITY(1, 1) NOT NULL , [EntityId] [INT] NULL , [...
0
votes
1answer
76 views

Debugging a hanging session/lock

We are having a recurring situation where we have 2 one query hanging, and other queries waiting for that one, and our DB grinds to a standstill. I am trying to debug this and am seeking help. The DB ...
2
votes
1answer
156 views

Queries from different tables locking each other (LCK_M_X)

Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it). I still suck, and will for a long ...
1
vote
0answers
49 views

How to truncate a table without locking it?

I have a SYSTEM's table AUD$ in ORACLE's database that is constantly being populated (whenever a DB user logs in to DB - a records is written to AUD$). The table AUD$ has like 100 million records (...
1
vote
1answer
52 views

Postgres long running query not blocking table operations

We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to ...
1
vote
1answer
174 views

Zabbix partitioning autovacuum causes locking

I have a Zabbix database on a postgresql backend that grew to ~1TB of disk space. The Zabbix housekeeper (a process for removing old data from the tables) was running for over 18 hours so I used this ...
-4
votes
2answers
167 views

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended? [closed]

I want to atomically reserve inventory for N objects for M users. I have two inventory tracking tables, one for global inventory and one for personal inventory. Global Inventory Table Columns: ...
0
votes
0answers
129 views

Determine whether SQLite database is locked

I've read other answers on how to detect whether the SQLite database is locked, and it suggests to use sqlite3_busy_handler/sqlite3_busy_timeout. I'm using Qt which has support of SQLite databases but ...
0
votes
1answer
54 views

How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

When I query sys.dm_tran_locks, a couple of questions arise: I can see there are some locks that are placed at KEY, PAGE, EXTENT, RID level (resource_type column). How to find out to which table or ...
0
votes
2answers
155 views

Why Table Lock Escalation is happening in my scenario

I have a table Table1 that is updated in small chunks using below query: update top (1000) Table1 set VarcharColumn1 = 'SomeValue' from Table1 where ID in (select ID from Table2) and ...