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.
869
questions
4
votes
2
answers
426
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
103
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
22
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
113
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
48
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
75
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
31
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
200
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
255
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) ...
- 129
3
votes
0
answers
57
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.5k
1
vote
0
answers
79
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
74
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 ...
- 151
0
votes
1
answer
93
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
70
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
145
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.3k