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.
650
questions
0
votes
1answer
16 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
39 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
50 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
32 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
13 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
37 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
36 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
14 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
144 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
67 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
281 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
68 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
52 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
74 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
37 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
22 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
21 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
110 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-...
0
votes
0answers
60 views
How does Optimistic concurrency interact with pessimistic concurrency in MSSQL
In MSSQL / Azure SQL, with Snapshots allowed and Read Committed Snapshot Isolation enabled, i have an application that does not use snapshot transactions explicitly (using the default RCSI everywhere)....
9
votes
1answer
726 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
57 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
58 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
42 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
93 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
36 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
153 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
80 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
136 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
51 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
132 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
34 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
43 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
126 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
160 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
61 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
39 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
124 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 ...
2
votes
1answer
135 views
Cannot set READ_COMMITTED_SNAPSHOT to OFF on Azure SQL
Me and my team are trying to use Azure SQL for our development environment and ran into some critical and unwanted differences in locking behaviour to our production environment. The main problem is ...
0
votes
0answers
21 views
Oracle lock getting more information
I have some locks which I found by:
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
(select SERIAL# from v$session where sid=a.sid) SERIAL#,
' is blocking ',
(...
0
votes
0answers
27 views
Is sp_getapplock recommended for long running locks (> 10 minutes)?
I'm trying to get rid of a Redis dependency in our application.
We notice that is worse for our uptime to have a dependency on both SQL and Redis, above the performance benefits Redis would give us.
...
13
votes
1answer
284 views
Why does this RX-X lock not appear in Extended Events?
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
1
vote
1answer
67 views
What is the fastest way to (session) lock table?
I have some triggers to log changes on a table to Log table.
On insert and delete I add row to the Log table and for update I add two rows.
The log table contain identity column, and I want the 2 ...
0
votes
1answer
45 views
CREATE TABLE in Postgres locks pg_class forever
I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class ...
0
votes
1answer
68 views
Lock Escalation happening while inserting few million records into a table in production
I want to insert few million records into a table in my production database.
I am not updating any existing rows.I am just doing
INSERT INTO TAB1
SELECT
COL1,COL2,COL3
FROM TAB2
This table has an ...
4
votes
1answer
104 views
What makes SQL Server prefer page locking over row locking?
I'm probably dumb for asking this, but I'm wondering WHY would SQL Server prefer page locking over row locking when running DML queries.
In my specific scenario, I'm seeing in various deadlock graphs ...
2
votes
1answer
105 views
SQL Server Sch-S locks on unrelated tables
I am a bit confused. I myself am MCSE and thought I would understand most parts of the SQL Server.
Now I have a stored procedure here, doing inserts and updates on two tables dbo.tabA and dbo.tabB. ...
5
votes
1answer
158 views
NOLOCK or Read Uncommitted locking / latching behaviours
I've read many, many different takes on how NOLOCK or the read uncommitted isolation levels function in terms of locks / latches taken.
When using a SELECT with NOLOCK or in the Read Uncommitted ...
