Skip to main content

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.

Filter by
Sorted by
Tagged with
1 vote
0 answers
39 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 ...
onets's user avatar
  • 11
1 vote
1 answer
38 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 ...
Rémi Pradal's user avatar
0 votes
0 answers
26 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 ...
Michal Charemza's user avatar
4 votes
1 answer
196 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#...
Michal Charemza's user avatar
0 votes
3 answers
204 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) ...
GuidoG's user avatar
  • 129
3 votes
0 answers
54 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 ...
SqlWorldWide's user avatar
  • 13.5k
1 vote
0 answers
77 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 ...
rrejc's user avatar
  • 111
5 votes
0 answers
72 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 ...
Merlin Nestler's user avatar
0 votes
1 answer
70 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 ...
Philipp Faster's user avatar
1 vote
1 answer
64 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 ...
bboy's user avatar
  • 15
2 votes
1 answer
134 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, ...
Marcello Miorelli's user avatar
1 vote
1 answer
148 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 ...
dokgu's user avatar
  • 123
0 votes
1 answer
117 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 ...
Just a learner's user avatar
0 votes
1 answer
65 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 ...
Everix's user avatar
  • 1
1 vote
1 answer
145 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 ...
user avatar
2 votes
1 answer
372 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 ...
brettkanker's user avatar
1 vote
0 answers
77 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 ...
Mohit Leekha's user avatar
1 vote
1 answer
160 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. ...
Ryan at Cloud Retailer's user avatar
1 vote
2 answers
169 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 ...
The amateur programmer's user avatar
1 vote
1 answer
55 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 ...
user1508072's user avatar
3 votes
1 answer
70 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 ...
sonus21's user avatar
  • 131
0 votes
2 answers
140 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,`...
TheDataGuy's user avatar
  • 1,950
-2 votes
2 answers
276 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 ...
J. Mini's user avatar
  • 501
1 vote
2 answers
123 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 ...
Marcus's user avatar
  • 378
1 vote
0 answers
64 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 ...
Noel Jons's user avatar
0 votes
0 answers
115 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 ...
Miguel Ping's user avatar
0 votes
1 answer
112 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 ...
J. Mini's user avatar
  • 501
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 ...
Justin Lowen's user avatar
1 vote
1 answer
170 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 ...
Felipe's user avatar
  • 305
0 votes
1 answer
46 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 ...
Woojin Lee's user avatar
1 vote
2 answers
347 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 ...
Daniel Benzie's user avatar
0 votes
0 answers
872 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....
gabtzi's user avatar
  • 161
0 votes
1 answer
72 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 --> ...
Alex F's user avatar
  • 101
0 votes
0 answers
24 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: ...
mark's user avatar
  • 9
0 votes
1 answer
51 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 ...
Jacqueline's user avatar
1 vote
1 answer
255 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 ...
jdx's user avatar
  • 13
0 votes
1 answer
278 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: ...
asn's user avatar
  • 127
0 votes
1 answer
462 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, ...
ryanrasa's user avatar
0 votes
1 answer
197 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 ...
Jacqueline's user avatar
2 votes
2 answers
722 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 ...
EgeronimoL's user avatar
4 votes
0 answers
286 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 | +-------...
Alex's user avatar
  • 161
5 votes
3 answers
220 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. ...
TRiG's user avatar
  • 221
0 votes
1 answer
272 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 ...
ishan verma's user avatar
0 votes
0 answers
329 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 ...
Paweł Sopel's user avatar
1 vote
1 answer
255 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 ...
Niels Broertjes's user avatar
0 votes
2 answers
1k 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 ...
Mio's user avatar
  • 651
3 votes
2 answers
282 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 ...
John Smith's user avatar
0 votes
2 answers
2k 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....
Sherman's user avatar
1 vote
1 answer
267 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, ...
Sotis's user avatar
  • 306
0 votes
2 answers
817 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 ...
AndCode's user avatar
  • 101

1
2 3 4 5
18