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
1
vote
2 answers

Lock a row for a while in one transaction and release lock in other transaction

It is a .Net application which works with an external device. When some entity (corresponds to the row in a table) wants to communicate with device, the corresponding row in the SQL Server table should be locked until the device return a result or…
Rzassar
  • 2,117
  • 1
  • 33
  • 55
1
vote
0 answers

How to resolve MySQL lock timeout error?

I am getting below listed error many times in my application log. 2015-05-19 10:27:57,842 ERROR [Timer-3] [org.hibernate.util.JDBCExceptionReporter] Lock wait timeout exceeded; try restarting transactionCouldn't retrieve InnoDB stat us due to…
Vishal Zanzrukia
  • 4,902
  • 4
  • 38
  • 82
1
vote
1 answer

How to obtain a row level lock in java when multiple JVMs accessing same data base?

I have three instances of same application running in different JVMs. I don't want a specific value in the data base to modified by different applications at the same time. How do I enforce this restriction in java?
Bhargav Kumar R
  • 2,190
  • 3
  • 22
  • 38
1
vote
0 answers

Test lock status of rows in MySQL/InnoDB

I am using the SELECT ... FOR UPDATE syntax to reserve multiple rows of multiple InnoDB tables in MySQL 5.6. This allows me to isolate concurrent write operations from each other. I am pretty sure that the locking works since I could prove that a…
Silicomancer
  • 8,604
  • 10
  • 63
  • 130
1
vote
1 answer

Can I lock a record from a join SQL statement using ROWLOCK,UPDLOCK?

I have a stored procedure to get the data I want: SELECT a.SONum, a.Seq1, a.SptNum, a.Qty1, a.SalUniPriP, a.PayNum, a.InvNum, a.BLNum, c.ETD, c.ShpNum, f.IssBan FROM OrdD a JOIN OrdH b ON a.SONum = b.SONum LEFT JOIN Invh c ON a.InvNum =…
Andrea.Ko
  • 119
  • 4
  • 12
1
vote
2 answers

What changes should be made to SQL Server’s locking architecture to make it more developer friendly?

I’ve lately come across a rather frustrating situation where SQL server refuses to issue locks only against a primary key when a statement like this select * from table with (rowlock updlock) where key=value is executed against it. Now don’t get me…
Middletone
  • 4,190
  • 12
  • 53
  • 74
1
vote
1 answer

Sybase read row with WriteNoPK using iSQL

i'm using Sybase SQL Anywhere 12. Let me say I have a Table user with the following connections to it: conn_name conn_id user_id table_type creator table_name index_id lock_class lock_duration lock_type row_identifier SQL_DBC_a2a1060…
Max
  • 33
  • 6
1
vote
2 answers

How can I atomically update a row with a timestamp?

I have a distributed application that uses the database to synchronize clients. Clients will attempt to update a record, but will only do so if no other client has done so in the past 1 hour. Here's the minified code and the dilemma: Say one…
Jay Sullivan
  • 17,332
  • 11
  • 62
  • 86
1
vote
4 answers

Ensuring unique numbers from a sql server database

I have an application that uses incident numbers (amongst other types of numbers). These numbers are stored in a table called "Number_Setup", which contains the current value of the counter. When the app generates a new incident, it number_setup…
Rob Gray
  • 3,186
  • 4
  • 33
  • 34
1
vote
2 answers

Multiple instances of same PHP script processing diffrent MySQL rows using rowlocking

What I want to do is to execute the same script every few minutes with cron. The script needs to process some data read from the database, so obviously I need it work on diffrent row each time. My concept was to use row locking to make sure each…
giveupyourname
  • 51
  • 4
1
vote
2 answers

sql server hint NOLOCK and ROWLOCK

I'm currently investigating an annoying problem on my website. We're giving away prizes regularly on the website, but to enter the competition people have to login. So the website becomes a lot busy at times. I found that when a lot of people trying…
Ray
  • 311
  • 2
  • 6
  • 15
0
votes
1 answer

MySQL transactions vs locking

Quick question/clarification required here. I have a DB table that will quite possibly have simultaneous updates to a record. I am using Zend Framework for the application, and I have read about two directions to go to avoid this, first being…
Aaron Murray
  • 1,920
  • 3
  • 22
  • 38
0
votes
0 answers

How to change the value of innodb_lock_wait_timeout from hibernate

We are using AWS Aurora 2.x database ( compatible with MySQL 5.7 ). From our application, we are interacting with database using hibernate. In one scenario, we want to use a custom value instead of the default one ( 50 seconds ). I was able to…
user93916
  • 1
  • 2
0
votes
1 answer

Are there PRECISE RULES to know when MySQL will apply gap blocking?

After several studies I see that MySQL applies gap blocking where it is not necessary. That's why I want to ask this question, is it something deterministic? I asked this question and BETWEEN SQL - why is there a gap lock? article is not clear to…
user19481364
0
votes
1 answer

row-level locks vs index record locks

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-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,…
user19551894
1 2 3 4
5
6 7 8