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
0
votes
1answer
42 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
48 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
117 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
20 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
109 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
17 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
30 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
53 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
144 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
16 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
33 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
60 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
34 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
268 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
66 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
37 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
57 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 ...
3
votes
1answer
90 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
82 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
36 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
36 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
27 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
68 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
52 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="...
1
vote
1answer
127 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
31 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
29 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
36 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
63 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
115 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
55 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 ...
2
votes
1answer
27 views

SQL Azure “hot” table query slowdown over time

I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size ...
4
votes
2answers
253 views

Gap Locking in Read Committed isolation level in Mysql

MYSQL VERSION : 5.7.X STORAGE ENGINE : Innodb I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
0
votes
1answer
73 views

mongodb read write locks

I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older ...
9
votes
1answer
491 views

Lock in SQL Server and Oracle [duplicate]

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference: In Oracle - I performed an update for one row without issuing commit or rollback and in another ...
0
votes
0answers
43 views

InnoDB locking: what does index record mean?

From MySQL documentation Record Locks A record lock is a lock on an index record. what does index record mean for cluster index? what does index record mean for secondary index? Is it a key-...
0
votes
0answers
95 views

MySQL InnoDB: how to interpret this InnoDB monitor output of Next-Key locks?

Next-Key Locks from MySQL documentation A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. InnoDB performs row-level ...
0
votes
1answer
12 views

Postgres 9.6.6 on Ubuntu 16.04, lots of errors

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we get giant logfiles under /var/log/postgresql table bloat on the database of the perfsonar ...
0
votes
0answers
57 views

Perform SELECT without Locking Table but don't allow Dirty Reads

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM ...
5
votes
1answer
143 views

SQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource

My understanding of locking in SQL Server has been that if one process holds an exclusive (X) lock on a resource e.g.a key row in an index, another process cannot acquire a Shared (S) lock on that ...
0
votes
1answer
39 views

Update Statement on one table takes locks on other tables

Given the following statement on the AdventureWorks database: UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01' If I open another query window and look at the ...
0
votes
1answer
539 views

Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE

Am running below query Query Alter table dbname.tablename add column column1 varchar(50), add column column2 text , ALGORITHM=INPLACE, LOCK=NONE; It takes more time to execute. Ideally running ...