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.
845
questions
0
votes
2
answers
20
views
Improve MySQL concurrent updates
Are there any suggestions to run the concurrent updates on a table (100+ concurrent updates on a specific row)? Same row, like updating the clicks count for an user.
UPDATE `tbl`
SET `cli`=`cli`+ 1,`...
- 1,910
-2
votes
2
answers
95
views
I've TABLOCKed a table. How can I read it?
I've TABLOCKed a table so I can run bulk inserts. How can I read it while it is still locked?
Under normal circumstances, I would call this obviously impossible. Luckily, there is one key factor that ...
- 47
1
vote
2
answers
31
views
Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O
On MySQL 8.0.33, I'm experiencing a form of contention (reproduced on multiple instances) that is not explained by the documentation.
As example:
I boot an instance
on a console client, I invoke a ...
- 368
1
vote
0
answers
40
views
Querying master.dbo.syslockinfo much slower on new Sql Server machine
After changing the machine running SQL Server 2019 Standard to a new one (Intel(R) Xeon(R) CPU E5-2667 v4 @ 3.20GHz, 128GB RAM, SAS SSD RAID10), I noticed a very significant drop in the performance of ...
- 11
0
votes
0
answers
42
views
MariaDB locking
I have a table W with a primary key, and a table T with a status column and an FK to W.
I am observing some random locking which I don't know where it originated:
T1 does the following:
select where ...
- 141
0
votes
1
answer
83
views
Is there a stronger guarantee of minimal logging when running INSERT INTO [...] WITH (TABLOCK) on a heap than on a B-tree?
The documentation lists the following requirements for bulk imports with minimal logging
The recovery model of the database is set to simple or bulk-logged.
The target table is an empty or non-empty ...
- 47
0
votes
1
answer
94
views
PostgreSQL - when is pg_advisory_xact_lock released, and when will persisted data be visible to other transactions
I am currently running PostgreSQL 13, but have also observed this behavior in PostgreSQL 11. I am running into an issue where a UniqueConstraint violation error is thrown, but I would expect ...
- 20
1
vote
1
answer
78
views
Why does VALIDATE CONSTRAINT on PG 14.7 take a ShareLock?
I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE ...
- 283
0
votes
1
answer
36
views
If I UPDATE one column in one row in the MySQL InnoDB engine, do I have to wait for the lock to be released when I SELECT another column?
For example, suppose you run the queries below.
UPDATE `user`
SET point=30
WHERE id=1;
SELECT nickname
FROM `user`
WHERE id=1;
In this case, should the second SELECT query wait for the lock from the ...
1
vote
2
answers
105
views
Locking/Isolation - MariaDB
In MariaDB the default isolation level is repeatable read. I understand this means when I open a transaction I will not see any concurrent writes to the DB.
I also have a situation where I need to ...
- 113
0
votes
0
answers
262
views
how can I find out what's causing the deadlock in mariadb?
I am trying to troubleshoot an issue in a magento 2 website. It seems that at random times there comes a situation where mariadb seems to go into a deadlock situation and slowly stops serving requests....
- 149
0
votes
1
answer
36
views
Simultaneous INSERTs which lock the target table inside a before-insert trigger result in a deadlock
I'm working on a web application which uses a PostgreSQL database for storage. There are many logical entities in this application which are related to each other like hotel --> hotel_floor --> ...
- 101
0
votes
0
answers
17
views
show engine= inno db status; found the deadlock and blokcing txn_id need to find the blocking query
trx_id: 19257543100
trx_state: RUNNING
trx_started: 2023-07-19 14:59:14
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: ...
- 9
0
votes
1
answer
32
views
Locking order in a nested query
I'm trying to prevent deadlocks in my code because i may use both in different parts of my project.
Do they lock rows in the same order?
Nested
select * from `carts`
where `carts`.`user_id` = ? and ...
- 57
0
votes
1
answer
141
views
PostgreSQL lock contention on pg_database
We run a write-heavy application backed by PostgreSQL. Our mainline has been running against PostgreSQL 11 for years and we have started rolling out a major update that runs against PG 15. I am ...
- 3
0
votes
1
answer
87
views
NoSQL vs SQL during sharding when a single read operation only hits one shard
I've a use case where I'm stuck between figuring out whether to use SQL or NoSQL db.
The db has 2 fields - A (PK), B
A is 8 char-long, while B is 100-400 char long.
The operations are simply:
Write: ...
- 117
0
votes
1
answer
229
views
MariaDB large number of sessions waiting on index_tree_rw_lock
I have a MariaDB RDS instance that is mostly saving batched insert statements to 4 tables. These inserts have batches of an average of 200/batch and each statement saves to a single table. However, ...
- 1
0
votes
1
answer
131
views
Transaction/For update lock for an eCommerce project
When users want to purchase their orders, The quantity of the products should be decreased. Now I'm planning to use a Transaction and a FOR UPDATE lock on the products and decrease the quantity of ...
- 57
2
votes
2
answers
463
views
How can I prevent race conditions in Cassandra?
Lets say two users simultaneously want to access the same partition of a Cassandra table:
Suppose we have a Cassandra table called "user_data" that stores user data. The table has a ...
- 21
4
votes
0
answers
143
views
MySQL: how to kill a user lock held by a terminated thread
We're using MySQL 8.0.32 hosted by AWS RDS.
mysql> SHOW VARIABLES LIKE 'version';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| version | 8.0.32 |
+-------...
- 161
4
votes
2
answers
92
views
Remove redundant disable/enable key statements from mysqldump
A standard mysqldump will create a table, disable keys, insert the data, and re-enable keys. There are reasons for this. However, when a table is empty, the keys are still disabled and re-enabled.
...
- 211
0
votes
1
answer
165
views
Is it safe to configure Lock Escalation as 'AUTO' for partitioned tables
LOCK_ESCALATION= AUTO.
In one of our Data warehousing product we use partitioned tables. They are partitioned based on an integer column. We have implemented partitioning here so we can easily switch ...
- 29
0
votes
0
answers
157
views
Refreshing materialized view blocks an unrelated table
Last few days I was experiencing a problem that seems really strange to me and even though I have some experience with PostgreSQL, I really don't understand what may be the cause.
We have two services ...
1
vote
1
answer
119
views
SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS
I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the ...
- 517
0
votes
2
answers
619
views
How to simulate FOR UPDATE lock on UPDATE on PostgreSQL?
I am trying to play with PostgreSQL 14 and row level lock on update. Documentation mentions:
The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the ...
- 631
2
votes
1
answer
192
views
Why innodb locks index till the end?
Why a query BETWEEN two IDs locks index till the end?
UPDATE elem SET c='' WHERE id BETWEEN 2 AND 5;
-- locks id>5 for some reason
The database is MySQL 8.0.33 GA.
Here's the full example, taken ...
- 21
0
votes
2
answers
764
views
Transactions are not completing when locking and updating one row at a time
I have a database function I have written to atomize a simple table update. My system is status driven, so I want the next row with the desired status, and update it to a new status, and return the ID....
- 1
0
votes
1
answer
154
views
How to drop a table referincing a very trafficated table easily?
Let's say I have this two tables
CREATE TABLE users (
id_user serial primary key,
name text NOT NULL
);
CREATE TABLE useless_table (
id serial primary key,
id_user int4 NOT NULL,
...
- 284
0
votes
2
answers
514
views
What is the limit on the number of transactions waiting for a lock in SQL Server?
I need to figure out the maximum throughput for updates of the table with a lock, as this is how the current solution is designed.
How many simultaneous updates can be queued internally by SQL server ...
- 101
-1
votes
1
answer
153
views
MariaDB / MySQL: Database Froze
We have had weird database freezes happen randomly. Usually, killing the troublesome process or thread helps, but in this case, nothing helped at all!
Nearly all of the queries in the process list ...
- 107
1
vote
1
answer
276
views
Would a row-level lock affect a DDL ALTER TABLE statement on Postgres?
Postgres version: 13.8
A week or so ago we ran a migration to safely drop a column on one of our tables. This column is not referenced by any other table and there was no index on the column. As far ...
- 13
2
votes
0
answers
467
views
Does Serializable isolation eliminate the need for explicit locks?
Postgres documentation on Serializable isolation level counterposes Serializable with explicit locking, and makes it sound as though explicit locking should not be needed when using Serializable. I'm ...
- 21
1
vote
1
answer
180
views
Under what conditions does WITH(rowlock) not work on views?
We have a lock pileup problem on a customer site. By a combination of tools we managed to locate the root locker. It is definitely running this particular SQL statement at the time of the lock pileup:
...
- 426
1
vote
1
answer
319
views
How to determine if a query can potentially deadlock in Postgres?
When implementing database queries I'm often wondering if the query I'm writing can potentially deadlock when executed in parallel by many clients. Often I only find out by running the query in ...
- 172
0
votes
2
answers
434
views
Postgres long transaction holding advisory lock
Is it a bad idea to try to hold a transaction open indefinitely in order to hold an advisory lock? It appears to function well enough for our use case, however in performance testing the long ...
0
votes
1
answer
581
views
New rows are locked by select on PostgreSQL replica
have a strange trouble with getting new rows on replica on PostgreSQL on AWS.
I have master database on AWS with constant flow inserts/updates not with big rate.
And these changes are replicated ...
- 3
1
vote
2
answers
886
views
Is it possible to add a GENERATED COLUMN to a large table without locking/downtime on postgres?
I am trying to add a new column as a generated column to a large table (over millions of rows). This generated column takes in the id column and performs a custom PostgreSQL function and returns it. I ...
- 11
1
vote
1
answer
254
views
What could be causing the delay in synchronous replication between the master and slave nodes?
I have 2 PostgreSQL 13 database, one acting as a master and the other as a slave, using streaming replication with synchronous_commit=remote_apply. This means that all data written to the primary ...
- 31
1
vote
1
answer
77
views
keeping order rows saved to calculate rank and percentile
we have a large table, around 12M records, everyday at midnight,
we run a query to sort all rows by a certain criteria and save the order in a column.
the reason we do that is because we have a ...
4
votes
1
answer
204
views
MySQL - Understanding what is causing deadlocks
I've researched the topic on the official documentation and other questions, however weeks into it I'm still failing to understand what's causing deadlocks in my application. It only happens in ...
- 141
2
votes
1
answer
979
views
Avoiding deadlocks when locking multiple rows without using NOWAIT
In a couple of answers I've been advised that NOWAIT is necessary to prevent deadlocks, which has surprised me because it's not mentioned in the Postgres documentation on locking and deadlocks
Given ...
- 581
2
votes
1
answer
492
views
How two phases locking ensures serializable?
I have read many sources, and they say one of the ways to implement serializable in the database is to use two-phase locking. But I don't really understand how two-phase locking could ensure ...
3
votes
1
answer
700
views
Will INSERT ON CONFLICT DO NOTHING lock the row in case of conflict?
In read-commited isolation level:
If I understood correctly, in case of no pre-existing rows that would result in conflict, two concurrent transactions with INSERT ... ON CONFLICT DO NOTHING - which ...
- 135
0
votes
1
answer
250
views
Does UPDATE FROM VALUES lock rows in the order they are listed?
I'm bulk updating some rows using UPDATE ... FROM (values ... as described in this answer.
Will the rows will be locked in the order that they appear in the values? Or do I need to do an explicit ...
- 581
3
votes
1
answer
386
views
MySQL Locking and Isolation Level
Could someone help me to understand this MySQL behavior?
I was trying to add a new column on a table using this statement:
ALTER TABLE `tb_example`
ADD COLUMN `score` INT NULL,
ALGORITHM = ...
- 133
0
votes
1
answer
295
views
Deadlock on Non Clustered Index
<deadlock>
<victim-list>
<victimProcess id="process1e4222068c8" />
</victim-list>
<process-list>
<process id=&...
- 167
2
votes
2
answers
860
views
What exactly is "only a read lock on the target table"
I'm trying to find information on whether it's safe to run ANALYZE on a large table that has stale statistics. The table is accessed in an "append-only" fashion and is constantly written to ...
- 133
2
votes
0
answers
64
views
How to derive a LOCK ID from a user lock handle?
I have a lock handle from dbms_lock.allocate_unique() but I would like to get the Lock ID from the handle. By ID I mean the value of SYS.DBMS_ALLOCATED_LOCK.LOCKID and V$LOCK.ID1. From what I can see, ...
- 666
0
votes
1
answer
76
views
Which two threads are handling which table and how to solve this?
I'm working on a multithreaded application (VB.Net/C#), who is working on an SQL-server database.
Regularly, my customer is complaining that the application show "deadlock"-like exceptions ...
- 523
2
votes
0
answers
151
views
deadlock on too many requests and DoS occurred, Why?
I ran into a strange monitoring DBA error "Deadlock" when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.)
Why did this occur?
I add the Deadlock XML Too:
...
- 121