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.
874
questions
1
vote
0
answers
15
views
PostgreSQL locking problem
TL;DR
We have a postgres stored procedure running as a job, which calls another stored procedure, which calls yet another stored procedure, and this internal stored procedure acquires ROW EXCLUSIVE ...
- 153
0
votes
2
answers
103
views
When does SQL Server use Row Lock and when does it use Page Lock?
Is there a deterministic way to know whether a T-SQL query on SQL Server will use row locks or page locks?
In the chapter Lock granularity and hierarchies of the documentation it reads:
To minimize ...
- 159
0
votes
1
answer
39
views
Locking write to tables while Postgres procedure is executing
I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt ...
- 103
1
vote
1
answer
44
views
Setting a value for max_locks_per_transaction
We find ourselves in a situation where we need to increase the value max_locks_per_transaction as the default isn't appropriate. I've spent some time looking and I can't find any information to ...
- 23
2
votes
1
answer
164
views
When Page Lock happen under Read Committed Isolation level in SQL Server
I have a clustered index table in Azure SQL Database. After checking the dm_db_index_operational_stats, the column page_lock_wait_count greater than 0 means there are some Page Lock happended for this ...
- 23
4
votes
2
answers
449
views
Best Way to Determine if a Table has a Long Running Transaction
I need to be able to iterate all tables and issue a TRUNCATE command, however, I don't want the loop to be held up by blocking. Essentially, I am looking for the best way to determine if a SCH-M lock ...
- 567
0
votes
1
answer
110
views
TRUNCATE WITH PARTITION Constantly Fails on 1 Out of 620 Tables
There is a table that seems to have constant updates. There is also a stored procedure that runs periodically and iterates through tables and attempts to truncate a partition of data using the command ...
- 567
0
votes
1
answer
24
views
PostgreSQL16 concurrency upsert rows with subquery in trugger funtion got maybe bad read
I have two tables:
create table name
(
id bigserial primary key,
name text not null
);
create table name_score
(
id bigserial ...
- 3
3
votes
1
answer
138
views
Locking for update inside CTEs
If I make a query using CTEs that looks like that:
WITH cte_a AS (
SELECT a.id, a.something
FROM a
WHERE a.something IS NOT NULL
),
-- [...] some other CTEs
cte_d AS (
SELECT
...
- 167
1
vote
0
answers
58
views
MySQL InnoDB Stored Procedure locks table?
MySQL 8.0
InnoDB
I have some sql statement like
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
INSERT INTO MyTable
SELECT Name
FROM MyTable
...
- 11
1
vote
1
answer
191
views
Locks acquired during drop table (postgres)
I have a table A, that is referencing two other tables B and C.
I would like to understand what's happening exactly when i am dropping A.
When dropping A my understanding is that an access exclusive ...
- 15
0
votes
0
answers
47
views
Is there a way to take a cross-database advisory lock in PostgreSQL?
This is a follow up to Advisory locks: private to connected database or shared across cluster? where it turns out that the space of advisory locks in PostgreSQL are private to a single database.
So is ...
- 263
4
votes
1
answer
204
views
Advisory locks: private to connected database or shared across cluster?
Is the space of keys in PostgreSQL advisory locks private to the connected database, or shared across the database cluster?
Nosing around https://www.postgresql.org/docs/current/functions-admin.html#...
- 263
0
votes
3
answers
372
views
Can using UPDLOCK and ROWLOCK help me fight of deadlocks in my situation?
I have a table tblImportData that looks like this
CREATE TABLE [dbo].[tblImportData] (
[ImportDataID] INT IDENTITY(1,1) NOT NULL,
[ImportFileNumber] VARCHAR(50) ...
- 139
3
votes
0
answers
62
views
request_status is Grant and Convert on the same resource_type at the same time
I have a typical blocking scenario due to lock contention.
Lead blocker has these locks on an object.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock ...
- 13.6k
1
vote
0
answers
84
views
SQL Server allows select on exclusive locked table
Microsoft SQL Server 2019 CU 15.
In the first session I execute the following statements:
begin transaction
update Ledger set AmountDebit = 1000 where AccountId = 14
which creates exclusive lock on a ...
- 111
5
votes
0
answers
75
views
Why is the lock on the key not held?
When analyzing a locking error in SQL Server I found a design decision which causes deadlocks.
I want to understand why this decision was made. As for context: If somebody updates a table I have a ...
- 159
0
votes
1
answer
127
views
What are disadvantages of using locktable?
I have many places in my application where users are doing something that should be non-concurrent in per-user-and-namespace bases. So that means that:
Two users can press the same button at the same ...
1
vote
1
answer
76
views
Is MariaDB Acquiring Exclusive Lock during Delete?
There is a Spring batch job with one delete and many inserts within a transaction with isolation level READ_COMMITTED:
delete from TABLE_A;
insert into TABLE_A values (...);
insert into TABLE_A values ...
- 15
2
votes
1
answer
171
views
RESTORE DATABASE - The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time
While doing a restore of a big database 1.8 TB into a different server
I came across this error message when the restore was about 90% completed:
90 percent processed.
Msg 3013, Level 16, State 1, ...
- 16.4k
1
vote
1
answer
274
views
How to lock an entire Postgres database
I have a process (for backup and replication using binary logs) that works in MySQL that I want to replicate for Postgres databases (using WAL):
Lock database (FLUSH TABLES WITH READ LOCK;)
Disable ...
- 123
0
votes
1
answer
128
views
Understanding Lock Behavior in SQL Server Deadlock Scenario with INSERT
Hello SQL Server Experts,
I'm currently experimenting with SQL Server deadlocks and have intentionally created one to better understand locking behavior. After setting up a test table, I ran two ...
- 2,042
0
votes
1
answer
76
views
How to set up locks to prevent stale data being written
I'm mainly a C#/Javascript developer, with only a basic understanding of SQL and databases. I've been doing some reading on transactions and locks, but I'm still struggling to figure out how to set ...
- 1
1
vote
1
answer
215
views
Why readers (SELECT) can be blocked by writers (DML) in SQL Server?
Could anyone explain to me why in SQL Server SELECT can be blocked by UPDATE statement? I thought, that MVCC (implemented in SQL Server by copying modified data to the tempdb) prevent readers to be ...
drk
2
votes
1
answer
552
views
SQL Server enabling page level locks on all of a databases's indexes
Good day everyone. Recently, our maintenance plan that reorganizes indexes has been throwing errors, and it's because page level locks have been disabled on some indexes for unknown reasons. The job ...
- 25
1
vote
0
answers
93
views
SQL job get blocked after some time by a process on master DB
I have a job which performs following steps on high level
Load Stage table
Load Fact table
Apply lots of rules on fact table, so lots of updates
This was working fine for more than a year but now it ...
- 11
1
vote
1
answer
192
views
Unexpected workload differences between SQL servers
So we've been using a workhorse of a DB server for just over 7 years now, last year we began easing into a new server that is essentially better in every way, transitioning one database at a time. ...
1
vote
2
answers
207
views
Understanding table locking in MS SQL TSQL Transactions
I wanted to make the old following query safe from data races when running them in parallel. The query checks if a row exists in in a table using specific criteria and if such row does not exist, it ...
1
vote
1
answer
71
views
Does a commit occur all at same time from a transactional point of view?
Assume a "Bank_Account" table exists and contains a single column "balance", where everyone has balance=100.
If a transaction performs "update Bank_Account set balance = 200 ...
- 113
3
votes
1
answer
117
views
Row Locks in Partitioned Aurora MySQL Table
I'm experiencing many row locks in a AWS Aurora AWS MySQL
MySQL version=8.0
Aurora Version=3.04.0
The table is pretty big close to 1 Billion rows. The table is partitioned in 24 partitions based on ...
- 131
0
votes
2
answers
197
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,950
-2
votes
2
answers
406
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 ...
- 661
1
vote
2
answers
173
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 ...
- 380
1
vote
0
answers
82
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
163
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
144
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 ...
- 661
0
votes
1
answer
2k
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 ...
- 36
1
vote
1
answer
197
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 ...
- 305
0
votes
1
answer
49
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
467
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
1k
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....
- 161
0
votes
1
answer
94
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
26
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
64
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 ...
- 77
1
vote
1
answer
289
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 ...
- 13
0
votes
1
answer
350
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: ...
- 127
0
votes
1
answer
535
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
232
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 ...
- 77
2
votes
2
answers
803
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
349
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