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
1answer
61 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
0answers
30 views

Most efficient way to insert records with unique sequential numbers?

The first record should have the serial number 0 (or 1) and each new record should get the next available sequential serial number scoped by a foreign key. What is the most efficient way to ensure ...
0
votes
1answer
25 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
54 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
84 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
71 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
139 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
79 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
43 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
40 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
35 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
32 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
19 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
56 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
21 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
47 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
62 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
22 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
25 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
30 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
34 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
72 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
54 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
26 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 ...
3
votes
2answers
147 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
23 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
447 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
29 views

Counter Field that Increments on Update?

I need a "counter" field that gets the latest value of a counter and then increments the global counter value so that the next update on a reocrd gets the new latest value. For example, lets say this ...
0
votes
0answers
25 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
55 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
48 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
128 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
28 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
371 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 ...
3
votes
1answer
299 views

Checking @@ROWCOUNT failing

I have a code similar to the one below in one of my SQL procedures, declare @rowcount int update table1 set value = @value where id = @id select @rowcount = @@ROWCOUNT if ( @rowcount = 0 ) begin ...
0
votes
0answers
108 views

Can't cancel query in Redshift

I have run a SQL in redshift and it hangs over 1 hour(Even doing explain on the SQL). I tried to use cancel/pg_cancel_backend/pg_terminate_backend to cancel/terminate the session. It seemed the ...
0
votes
1answer
32 views

Differences of MySQL locking for update with WHERE and IN

So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked. ...
0
votes
1answer
34 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
0
votes
0answers
44 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
0
votes
0answers
227 views

Steps to unlock a locked select in postgres

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins. Since it was taking too long, I cancelled it. Now I'm trying to run a query to count records from the inner join but ...
1
vote
2answers
76 views

Overly eager explicit row locking causes deadlock

I'm running into deadlock problems on MariaDB using the INNODB engine. In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
1
vote
1answer
37 views

Recalculate and replace large table without interrupting table reads

There's a large table in the database containing statistical information. This table must be read frequently (by lookups which return a single row). This table must be completely recalculated every ...
1
vote
0answers
40 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
3
votes
1answer
81 views

Causes for pg_class locks except DROP TABLE

I do experience the following situation on a PostgreSQL 11: A transaction with unknown statements creates an ACCESS SHARE lock on pg_class. Another connection wants to execute DROP TABLE which causes ...
-2
votes
2answers
71 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
0
votes
1answer
520 views

Oracle Database troubleshooting enq: TX - row lock contention

AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average ...
0
votes
3answers
94 views

SELECT … FOR UPDATE returns old value after waiting for lock

It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from ...
0
votes
1answer
61 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...