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.
635
questions
0
votes
1answer
16 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
17 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'...
4
votes
1answer
100 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
57 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
684 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
35 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
47 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
38 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
66 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
34 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 ...
1
vote
3answers
100 views
How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries
Given the following table structure, records are being continuously inserted with the update_time of a very recent datetime. A separate Connection to the DB is periodically trimming old records with ...
0
votes
1answer
43 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
54 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
119 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
24 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
112 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
22 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
32 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
73 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 ...
-5
votes
2answers
148 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
29 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
37 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
78 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
58 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
20 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
23 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
278 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
40 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
62 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
103 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
91 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
152 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 ...
0
votes
0answers
11 views
mysql innodb locking
In one transaction I'm inserting a lot of rows into table T1.
"After insert" trigger of table T1 inserts into table T2.
Is it possible during that process select from T2 in another transaction? Or ...
2
votes
3answers
90 views
when do lock actually happen in a transaction?
Let's say we have a transaction A:
BEGIN TRAN A;
//--------------------< time a
SELECT productid, unitprice
FROM Production.Products
WHERE productid = 2;
//--------------------< time b
...
0
votes
1answer
44 views
inconsistent read before/after checkpoint?
Let's say the transaction in connection 1 tried to modify record A and then commit.Because the commit only flush log record in buffer to disk, it's checkpoint's job to flush all dirty page to disk (...
-3
votes
1answer
41 views
System failure causes inconsistency between two transactions
Let's say the picture above the transaction 4 for user A.
A updated a record from Account table, change the balance from 100 to 0, then a checkpoint occurred, so all dirty pages got written to disk.
...
0
votes
1answer
37 views
How is a table locked regarding UPDATE-SELECT queries in PostgreSQL?
Is it possible to combine these UPDATE queries?
UPDATE myTable1 SET fld1 = myTable2.value
FROM myTable2
WHERE myTable1.id = myTable2.id AND myTable2.style = 45;
UPDATE myTable1 SET fld2 = myTable2....
0
votes
2answers
38 views
Are race conditions possible in SQL? [closed]
I would like to understand what the SQL standard says about parallel queries and race conditions. If there is no standard, then I would be interested in the approach adopted by popular databases.
I ...
0
votes
0answers
40 views
Troubleshoot locks for sysschobjs objects in SQL Server
an application running on our SQL Server 2016 instance creates a lot of key locks for sysschobjs objects. By using the query:
with tbl as
(
select l.request_session_id,
l.resource_database_id,...
2
votes
0answers
72 views
Range lock on not existing record [duplicate]
We have some sort of logging table where a record gets inserted whenever a thread is working on a certain topic. It's possible that two separate threads try to start working on the same topic. ...
0
votes
1answer
24 views
In postgres, is using a lock better than transaction when reading data?
I have a race condition in the code for my database, and I'm wondering how I can prevent this using either locks or transactions? (And which is best)
I'm using an ORM to update values, however the ...
1
vote
1answer
56 views
Deadlock on the same resource calling the same procedure
I would like to kindly ask community for help with this deadlock scenario:
<deadlock-list>
<deadlock victim="process17392264e8">
<process-list>
<process id="...
2
votes
1answer
167 views
How do we get around PostgreSQL autovacuum taking a table-level lock ACCESS EXCLUSIVE on replicas?
We are running:
user@primary/client-n:~$ psql -d database -c "SELECT version();"
version
--------------------------------------------...
0
votes
1answer
41 views
ALTER TABLE SET LOGGED locked by autovacuum
metrics is an unlogged table that has around 3M rows, I have just executed the ALTER TABLE metrics SET LOGGED to keep this data logged. But it seems to be locked by some autovacuum running process.
...
0
votes
1answer
32 views
Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)
I am getting
"ERROR: out of shared memory"
presumably because my plpgsql function drops/creates a lot of temporary tables.
Searching for a solution to the issue, I found this answer that says:
...
1
vote
0answers
39 views
Exclusive lock vs atomic(?) increment/decrement in MySQL
We're working on implementing an account with virtual currency in our app and we need to update account balance (classic, yeah?).
Let's say we have an accounts table with user_id and balance fields.
...
1
vote
0answers
72 views
Why does an UPDATE to a full-text-indexed column lock the full text index?
I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was ...
1
vote
0answers
135 views
The GET_LOCK() and RELEASE_LOCK() functions are taking too long
We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
-2
votes
1answer
56 views
SSMS Application Security idle session locking window [closed]
We are running multiple versions of SQL Server Management Studio in our network, I believe from 2000 through 2017. For our security review we need to find out how to set an inactivity timeout in the ...
