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
59
votes
3 answers

Is it possible to force row level locking in SQL Server?

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?
Elan
  • 6,084
  • 12
  • 64
  • 84
43
votes
4 answers

How many rows will be locked by SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?

I have a query with the following structure: SELECT ..... WHERE status = 'QUEUED' ORDER BY position ASC LIMIT 1 FOR UPDATE; It's a single-table SELECT statement on InnoDB table. Field position (INT NOT NULL) has an index on it. status is ENUM and…
Vladislav Rastrusny
  • 29,378
  • 23
  • 95
  • 156
35
votes
5 answers

How do I lock on an InnoDB row that doesn't exist yet?

How can I guarantee that I can search if a username exists in my database, then insert that username into the database as a new row without any intercept between the SELECT and INSERT statements? Almost as if I am locking on a row that doesn't…
xLite
  • 1,441
  • 3
  • 15
  • 28
18
votes
3 answers

How to totally lock a row in Entity Framework

I am working with a situation where we are dealing with money transactions. For example, I have a table of users wallets, with their balance in that row. UserId; Wallet Id; Balance Now in our website and web services, every time a certain…
Zapnologica
  • 22,170
  • 44
  • 158
  • 253
16
votes
2 answers

When exactly MySQL locks a row on updating InnoDB table?

If I have this multiupdate query UPDATE user u INNER JOIN user_profile up ON up.user_id = u.id SET u.name = 'same_name_i_already_had', up.profile.age = 25 WHERE u.id = 10 Let's suppose the row 10 in user table already has the name…
Emilio Nicolás
  • 2,554
  • 5
  • 22
  • 29
11
votes
2 answers

Row Level Locking in Mysql

I have 5 rows in a table (1 to 5). I want row 2 lock for some update and in the meanwhile if someone tries to update row 4, then he should able to update. I am trying this with code below, but I feel its placing lock on table level rather than row…
Irfan Ahmad
  • 119
  • 1
  • 2
  • 9
11
votes
3 answers

Concurrent execution in SQL Server

Table schemas (SQL Server 2012) Create Table InterestBuffer ( AccountNo CHAR(17) PRIMARY KEY, CalculatedInterest MONEY, ProvisionedInterest MONEY, AccomodatedInterest MONEY, ) Create Table #tempInterestCalc ( AccountNo CHAR(17)…
Esty
  • 1,882
  • 3
  • 17
  • 36
7
votes
4 answers

Are data rows locked when editing them in SQL Server Management Studio 2008?

When I right-click on a table in SQL Server Management Studio and choose 'Edit top 200 rows', at what point, if ever, would the data that I'm looking at be locked? A colleague of mine stated that when viewing data in this way, the data rows can be…
Jason Evans
  • 28,906
  • 14
  • 90
  • 154
7
votes
1 answer

UPDATE with SELECT, will it lock each row or all SELECTed records

It is unclear to me (by reading MySQL docs) if the following query ran on INNODB tables on MySQL 5.1, would create WRITE LOCK for each of the rows the db updates internally (5000 in total) or LOCK all the rows in the batch. As the database has…
ddinchev
  • 33,683
  • 28
  • 88
  • 133
6
votes
2 answers

MySQL - Update table rows without locking the rows

I have requirement where we need to update the row without holding the lock for the while updating. Here is the details of the requirements, we will be running a batch processing on a table every 5 mins update blogs set is_visible=1 where some…
Lohith MV
  • 3,798
  • 11
  • 31
  • 44
6
votes
1 answer

SQL Server ROWLOCK over a SELECT if not exists INSERT transaction

I have upgraded from SQL Server 2005 to 2008. I remember that in 2005, ROWLOCK simply did not work and I had to use PAGELOCK or XLOCK to achieve any type of actual locking. I know a reader of this will ask "what did you do wrong?" Nothing. I…
IamIC
  • 17,747
  • 20
  • 91
  • 154
5
votes
1 answer

update with rowlock in MSSQL server

I was trying to understand ROWLOCK in SQL server to update a record after locking it. Here is my observation and would like to get a confirm if ROWLOCK is like a table or page lock sort of thing or I have not tried it correctly. ROWLOCK should be a…
NoNaMe
  • 6,020
  • 30
  • 82
  • 110
5
votes
1 answer

Where to use ROWLOCK, READPAST with CTE, Subquery and Update?

In trying to avoid deadlocks and synchronize requests from multiple services, I'm using ROWLOCK, READPAST. My question is where should I put it in a query that includes a CTE, a subquery and an update statement on the CTE? Is there one key spot or…
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
4
votes
2 answers

Multiple Update statements on a single row at exactly same time

I've a scenario where I get a cab request from a customer and I send pickup request to multiple drivers. Now Imagine if two of the drivers who got the request hit the "Accept" button at the exact same time, then which driver will get the ride. I've…
Deepak Singh
  • 610
  • 1
  • 7
  • 23
4
votes
1 answer

Difference between table and row locks

I'm studying about MySQL and how it works, and something confuses me and I don't find any clear explanation on the web about this. What exactly is the difference between row and table locks? One locks the row and the other locks the table. Correct?…
Jonez
  • 55
  • 1
  • 7
1
2 3 4 5 6 7 8