Stack Overflow
Stack Overflow

Questions tagged [rowlocking]

Row locking refers to when a database record is updated and the SQL engine locks the row to ensure referential integrity

Row locking is more common in modern RDBMS than in older ones. Row locking avoids collisions and deadlocking much more readily by locking only rows that are being edited. Locks are most commonly used with database , where a lock is requested and granted before any data is updated. Locks help ensure referential integrity, especially with regards to .

In , for instance, the engine would use table locking. When the engine was released it became popular quickly, in no small part because it offered row locking. InnoDB is now the default engine for MySQL 5.5 and later.

Helpful links

110 questions
0
votes
0 answers

Select query hangs when a large number of rows have an update lock

I am designing a program that will read a queue table. I am using locking to make sure that multiple instances do not pull the same row. I am locking rows like this: BEGIN TRANSACTION UPDATE top(10) Source with (ROWLOCK, READPAST, updlock) …
Don Chambers
  • 3,798
  • 9
  • 33
  • 74
0
votes
1 answer

Postgres row locking

I am new to Postgres(even to SQL). I am building a key DB which has a table named Key. My question is how can I perform the following on pg. If one connection is reading the first record and simultaneously the second connection comes in, it should…
AnswerRex
  • 180
  • 1
  • 1
  • 11
0
votes
0 answers

MySQL update taking more time with concurrent user hit

I have a spring boot application where I'm running a native query. UPDATE users SET status = "A" , user_id = '1234567' WHERE card_id = ( SELECT card_id FROM ( SELECT * FROM users d WHERE d.status = "U" LIMIT 1 ) d1) AND status = "U"; In…
1729i
  • 57
  • 1
  • 5
0
votes
0 answers

why for SQLCOM_UPDATE_MULTI, it lock other table with lock_s (INNODB )

innodb READ-COMMITTED: table: CREATE TABLE `test` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8; CREATE TABLE `test1` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL,…
褚华兴
  • 23
  • 4
0
votes
0 answers

Laravel pessimistic locking locks entire table instead of a single row

Laravel Version: 7.15 PHP Version: 7.4.6 Database Driver & Version: MariaDB 10.4.11 Below code is going to return array response for each invoices. Once action method called for id=1 it takes 20 sec to get committed. When the same method called for…
Navaneeth Raman Bhaskar
  • 35
  • 10
0
votes
1 answer

Prevent two threads from selecting same row ibm db2

I have a situation where I have multiple (potentially hundreds) threads repeating the same task (using a java scheduled executor, if you are curious). This task entails selecting rows of changes (from a table called change) that have not yet been…
Connor Butch
  • 648
  • 1
  • 10
  • 28
0
votes
0 answers

The use (UPDLOCK, READPAST, ROWLOCK) with conditional list SQL Server

I have a question about the using an expression in UPDATE (UPDLOCK, READPAST, ROWLOCK). When I use to specific row I know it works, but when I use a condition that allows search lists I don't know work, for example: Specific row UPDATE TABLE FROM…
user8223022
  • 195
  • 2
  • 15
0
votes
1 answer

MS-SQL Server selecting rows, locking rows. Unique returns

I'm selecting the available login infos from a DB randomly via the stored procedure below. But when multiple threads want to get the available login infos, duplicate records are returned although I'm updating the timestamp field of the record. How…
Samet S.
  • 475
  • 1
  • 10
  • 21
0
votes
1 answer

Is SaveChange transaction committed if db connection is lost?

We have implemented an WordAddin and use EF6 to connect to the oracle database. With the addin the user can load a document from the database. After editing the document the user closes the document. We use the DocumentBeforeClose-Event to update…
spoerl
  • 1
0
votes
2 answers

Atomic Select & Update. Make row invisible or unselectable by more than one process

The situation: PostgresSQL database. Application with SQL Alchemy ORM (not really important). Table with millions of rows. Hundreds of processes access the database with that table. Each wants to select one row and perform a relatively expensive…
Coykto
  • 1,014
  • 9
  • 12
0
votes
0 answers

Sql Server: Apply row lock excluding already locked rows

I will explain my scenario with an example. I have multiple rows in my table. I am picking those one by one for processing. I need to lock the row for processing. Sample code looks like, select top 1 * into #open_order from orders with (xlock)…
Sayuj
  • 7,464
  • 13
  • 59
  • 76
0
votes
2 answers

Same data for different key in table takes lots of time

I have a table... FactorID, col1 col2 Factor Value 1 a 2 1231 1 b 3 2342 2 a 2 1233 2 b 3 2344 That mean ... For factor Id 1, I have 1500 records...and factor Id 2 I…
Relativity
  • 6,690
  • 22
  • 78
  • 128
0
votes
1 answer

What is the default transaction row level in Postgres and/or LOCK used by default in Sequelize.js

What is the default row-level lock mode used in PostgreSQL? And because the question is related to Sequelize ORM, so, it would be nice to know which mode is used by default to avoid or modify my queries.
ncardez
  • 139
  • 2
  • 11
0
votes
0 answers

SQL index option Allow row lock, Allow page lock, should be set on or off? Is that possible to create periodically updated index?

I have a very heavy update transaction based table I am using an index to speed up pagination query However the results read from the table is not important. I mean it doesnt have to be fully updated So what actually these options do? I mean lets…
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
0
votes
1 answer

Find session of locked row

I am experiencing row lock contention in my oracle DB. I tried to kill some session to unlock them, but this rows are still locked. I know exact which row are locked. Can I find the session ID that has locked this row. I can get the ROWID of that…
Jafar Ali
  • 1,084
  • 3
  • 16
  • 39
1 2 3 4 5
6
7 8