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
2
votes
1 answer

postgresql db table locking or row locking multi query execution

I would like to understand how postgreSQL is doing multi query execution for example i have a DB that there are lots of insert queries running for example 20-40 per minute and lots of select queries like 200-300 per minute (simple query by primary…
Emrah Mehmedov
  • 1,492
  • 13
  • 28
2
votes
0 answers

Implementing a work queue in postresql

I want to make a persistent job queue in postgresql. So that multiple workers can select one job from the queue (using select for update with skip locked), process it and than delete it from the queue. I have a table: create table queue ( id…
microo8
  • 3,568
  • 5
  • 37
  • 67
2
votes
1 answer

MySQL row lock and atomic updates

I am building a "poor man's queuing system" using MySQL. It's a single table containing jobs that need to be executed (the table name is queue). I have several processes on multiple machines whose job it is to call the fetch_next2 sproc to get an…
skb
  • 30,624
  • 33
  • 94
  • 146
2
votes
2 answers

Oracle dirty read

I'm new using Oracle database 11g. I was testing table and row locks using SQL Developer. I have a row in my table like this: id : 1 desc: 'abc' I did an update without commit to change the field desc to 'zxc'. In another session I did a select…
user2876735
  • 33
  • 5
2
votes
1 answer

Long queue times for oracle row locks

I keep running into "enq: TX - row lock contention", when I run the Sql command below in a oracle 9 DB. The table mytable is a small table, with less than 300 lines. UPDATE MYTABLE SET col1 = col1 + :B3 , col2 = SYSDATE WHERE :B2 …
kurast
  • 1,660
  • 3
  • 17
  • 38
2
votes
0 answers

C# - Entity Framework lock record between operations

I have an application that implements entity framework 4.1, i use a context instance for every method object select() { using(DBContext context = new DBContext()) { return context.table.Where(e => e.id == 1).FirstOrDefault(); …
Chuy
  • 53
  • 2
  • 6
2
votes
2 answers

oracle row contention causing deadlock errors in high throughtput JMS application

Summary: I am interested in knowing what's the best practice for high throughput applications that have bulk messages trying to update the same row and get oracle deadlock errors. I know you cannot avoid those errors but how do you recover from them…
Robin Bajaj
  • 2,002
  • 4
  • 29
  • 47
2
votes
2 answers

Two different transactions updating different rows of same table at same time

Environment: SQL SERVER 2008 R2, Windows. CONNECTION-1: executing following BEGIN TRANSACTION UPDATE Check_lock with (rowlock) set LayoutType = 98 where USERID between 1 and 7; WAITFOR DELAY '000:10:00'; COMMIT…
Imran Amjad
  • 31
  • 1
  • 3
2
votes
3 answers

Can you do row locking in Access?

Can you do row locking in Access/JET? For example, can you do the following in JET SQL: UPDATE Test WITH (ROWLOCK) SET UpdateDate = myDate WHERE PrimaryKey = myKey
CJ7
  • 22,579
  • 65
  • 193
  • 321
1
vote
1 answer

Do I need UPDLOCK hint in this SP? Reading a Data Queue Row More Than Once

My DB Schema is Job -> (Many) JobData -> (Many) Results. A Job is broken into multiple JobData rows so that multiple threads can process 'chunks' of the Job (each JobData row). Then the processing threads insert Result row(s) for each of the…
Terry
  • 2,148
  • 2
  • 32
  • 53
1
vote
1 answer

Which of the several sessions waiting to acquire an exclusive row lock will be notified first upon release in Oracle RDBMS?

Suppose the following: There is a user session which holds exclusive locks on several rows in the table. Two (or more) other sessions are started, each with the request to acquire some of the currently held locks To make the case easier, let's…
M. Prokhorov
  • 3,894
  • 25
  • 39
1
vote
0 answers

BETWEEN SQL - why is there a gap lock?

CREATE TABLE justpk ( A INT, B INT, PRIMARY KEY (A) ); INSERT INTO justpk (A, B) VALUES (1, 1), (4, 1), (5, 1); +---+------+ | a | b | +---+------+ | 1 | 1 | | 4 | 1 | | 5 | 1 | +---+------+ Session1: begin; SELECT * FROM…
user19481364
1
vote
1 answer

when exactly does a lock pseudo-record supremum occur?

I need a example, please What do you mean by applying the lock to a pseudo-record? https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-next-key-locks For the last interval, the next-key lock locks the gap above the largest value in…
user19551894
1
vote
1 answer

Next-key lock explication - Primary key for range

I have the following query, and I wanted to use the diagram to CONFIRM IF I UNDERSTAND IT RIGHT: SELECT * FROM table WHERE pk > 99; "pk" is the primary key I am having trouble understanding the next key lock, I found this diagram to know which gap…
user19481364
1
vote
1 answer

InnoDB Locking - Does record lock use indexes?

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks Record Locks A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting,…
user19481364
1 2
3
4 5 6 7 8