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

MySQL: INSERT blocked by an UPDATE of the foreign key referenced row

Let me start my question with an SQL example. Here is the table setup: Create table x and y. With y.x refers to x.id. Insert a row into x (id=1). START TRANSACTION; CREATE TABLE `x` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `value` INT(11)…
HelloSam
  • 2,225
  • 1
  • 20
  • 21
4
votes
0 answers

How to use rowlock and readpast with NHibernate?

I have an application which currently reads data from a table using the following stored procedure: CREATE PROCEDURE [dbo].[GetBatchOfEmails] @BatchSize INT AS BEGIN SET NOCOUNT ON; WITH ResultSet AS ( SELECT TOP(@BatchSize)…
Øyvind
  • 1,600
  • 1
  • 14
  • 33
4
votes
1 answer

phalcon row locking with models

Since forUpdate still don't work (https://github.com/phalcon/cphalcon/issues/2407), what is best way to lock SELECTed rows in db? I have a innodb table with items to process. I start via cronjob some tasks, which look after items to process…
Glueckstiger
  • 107
  • 1
  • 7
3
votes
1 answer

SELECT FOR UPDATE and WHERE condition on two indexed columns - what's locking strategy?

This question is a logical continuation of this one: How many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE? Suppose I have such SELECT: SELECT id, status FROM job WHERE status = XXX AND id IN (1, 2, 3) FOR UPDATE id is primary…
Vladislav Rastrusny
  • 29,378
  • 23
  • 95
  • 156
3
votes
0 answers

Does Row Locks still exist after a deadlock?

So In this image my teacher wrote in red that after the deadlock appeared the transaction1 is able to set x-lock and load the old data. But after I tried it in SQL I have to wait because apparently the transaction2 still has seted the rowlock and a…
TiZaLjubavNisiRodjena
  • 117
  • 1
  • 7
3
votes
1 answer

MySql InnoDB increment and return a field in a transaction

In my application I want to take a value from an InnoDB table, and then increment and return it within a single transaction. I want also lock the row that i am going to update in order to prevent another session from changing the value during the…
QuickOrBeDead
  • 41
  • 4
3
votes
1 answer

PHP - MySQL Row level locking example

I've seen many posts explaining the usage of Select FOR UPDATE and how to lock a row, however I haven't been able to find any that explain what occurs when the code tries to read a row that's locked. For instance. Say I use the…
NEW2WEB
  • 503
  • 2
  • 8
  • 22
3
votes
2 answers

Regarding SQL Server Locking Mechanism

I would like to ask couple of questions regarding SQL Server Locking mechanism If i am not using the lock hint with SQL Statement, SQL Server uses PAGELOCK hint by default. am I right??? If yes then why? may be its due to the factor of managing too…
Mubashar
  • 12,300
  • 11
  • 66
  • 95
3
votes
3 answers

MySQL InnoDB locking only the affected rows?

I have an InnoDB table containing users, like this: +--------------+-----------------------+ | user_id | name | +--------------+-----------------------+ | 1 | Bob | | 1 | Marry …
Chad
  • 2,365
  • 6
  • 26
  • 37
3
votes
1 answer

sql server compact deadlock caused by page-lock on index

I am working on a c# desktop application using NHibernate as data access layer and SQL Server compact. The application uses multiple threads to perform selects and updates of data. In general it is working fine but sometimes deadlock situations…
Wolfgang
  • 3,460
  • 3
  • 28
  • 38
3
votes
2 answers

I need row-level locking

This is an extension to Is it possible to force row level locking in SQL Server?. Here is the use case I have accounts table having account numbers, balances, etc. This table is being used by many applications. It is quite possible that while I am…
bjan
  • 2,000
  • 7
  • 32
  • 64
2
votes
2 answers

Unit-testing PostgreSQL row-level locks

I am currently adding unit tests to a rather large quantity of PostgreSQL stored procedures, using pgTap. Some of the procedures perform operations which lock rows explicitly. These locks are critical to the application. How do I write tests that…
E.Benoît
  • 796
  • 6
  • 9
2
votes
0 answers

How to enforce 1-[max n] relationship concurrency-safely? Semaphore table?

I have the following minimal example showing my problem: I have a Person table, which has an (auto-increment) id as primary key and some information about the Person. I have a Widget table, which likewise has an (auto-increment) id is primary key…
JMC
  • 1,723
  • 1
  • 11
  • 20
2
votes
1 answer

SELECT FOR UPDATE subquery not respecting LIMIT clause under load

I have the following query that uses the psql SKIP LOCKED feature to poll messages from a queue table sorted by the insertion time. My subquery also limits the result set by 10 since that is the maximum number of results that can be processed by the…
Dan
  • 1,805
  • 2
  • 18
  • 21
2
votes
0 answers

Locking of tables in MySQL seems that they aren't able to handle the parallel request?

I locked the table using FOR UPDATE with an IF validation. However, it seems that they aren't able to handle the parallel request? I have two samples of stored procedures. I tested these using cURL to make a parallel requests. DROP PROCEDURE IF…
Ramil
  • 21
  • 3
1
2
3 4 5 6 7 8