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

MySQL 8.0 (InnoDB) update statement locking more rows than expected, causing deadlocks when called from Tomcat JDBC connection pool

I have a simple table with a one-column VARCHAR(32) primary key, with about 8000 rows. It has no other indexes except the primary key. I'm updating individual rows in this table quite rapidly, about 50-60 times per second, via a Tomcat 10 servlet…
John Daley
  • 121
  • 1
  • 5
1
vote
1 answer

Serializable and Row-based locking in MySQL

I'm trying to convert a database from MsSQL to MySQL. How exactly should the transaction configuration be done in MySQL for the Serializable isolation level that exists in MsSQL? Because MsSQL keyword didn't work. Example; SET TRANSACTION ISOLATION…
Pınar Seçkin
  • 47
  • 6
1
vote
1 answer

Difference between a row lock and a table lock in Oracle database

What is the difference between row locking and table locking in Oracle Database? Would a for loop with the UPDATE statement trigger a table lock?
RU Ahmed
  • 558
  • 4
  • 23
1
vote
0 answers

To use postgresql "SELECT FOR UPDATE" in EF Core with linq - FromSqlRaw

Im trying to use the postgresql's ROW LOCK in EF Core. I have a .net core application and postgresql database. In GetUserContainer function, I build my queries using the FromSqlRaw, as below var query = db.UserContainers.FromSqlRaw($"SELECT *, xmin…
Trupti
  • 108
  • 2
  • 14
1
vote
1 answer

Is it neccessary to unlock a table after a commit while row level locking?

I'm writing some code that uses row level locking with MySQL (innodb backend). The pseudocode is: START TRANSACTION SELECT * FROM foo WHERE foocondition FOR UPDATE UPDATE foo set bar=value WHERE foocondition COMMIT I can't find on the mysql…
SimoneLazzaris
  • 329
  • 2
  • 9
1
vote
1 answer

How can I force MySQL to obtain a table-lock for a transaction?

I'm trying to perform an operation on a MySQL database table using the InnoDB storage engine. This operation is an INSERT-or-UPDATE type operation where I have an incoming set of data and there may be some data already in the table which must be…
Christopher Schultz
  • 20,221
  • 9
  • 60
  • 77
1
vote
0 answers

SQL Server: Using ROWLOCK on UPDATE with OUTPUT

I have a table with the following structure: ID NAME STATUS 1 Item1 Pending 2 Item2 Pending 3 Item3 Pending ........................ I use a stored procedure to update the first N rows of the table,…
Gilbert Nwaiwu
  • 687
  • 2
  • 13
  • 37
1
vote
1 answer

Rowlock and lock escalation

I'm trying to use rowlock to prevent certain rows being updated while it's running but the problem is that I can't use the tables at all while running this: set transaction isolation level repeatable read; go begin try begin…
dstr
  • 8,362
  • 12
  • 66
  • 106
1
vote
3 answers

Teradata - how to select without locking writers? (LOCKING ROW FOR ACCESS vs. LOCKING TABLE FOR ACCESS)

I am developing an application which fetches some data from a Teradata DWH. DWH developers told me to use LOCK ROW FOR ACCESS before all SELECT queries to avoid delaying writes to that table(s). Being very familiar with MS SQL Servers's WITH(NOLOCK)…
Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
1
vote
0 answers

Is there any disadvantage of rowlock mechanism in mysql?

I am beginner to mysql but little basics are cleared but I have some doubt about locking system of mysql. Row lock mechanism is provided by InnoDB engine in mysql and it improves table performance as compared to table lock. But my Question is little…
Rajesh Navagare
  • 47
  • 9
1
vote
2 answers

Is it possible to lock a row with SELECT statement in transaction in SQL SERVER

Is it possible to lock a row with SELECT statement in a transaction, in SQL SERVER? I want to lock the row, so other transactions from outside cannot reach that row. After the transaction is committed or rollbacked, the row should be released. Here…
Arif YILMAZ
  • 5,754
  • 26
  • 104
  • 189
1
vote
1 answer

FOR UPDATE doesn't seem to lock the row in MySql InnoDB

MySql = v5.6 Table engine = InnoDB I have one mysql cli open. I run: START TRANSACTION; SELECT id FROM my_table WHERE id=1 FOR UPDATE; I then have a second cli open and run: SELECT id FROM my_table WHERE id=1; I expected it to wait until I either…
Force Hero
  • 2,674
  • 3
  • 19
  • 35
1
vote
0 answers

It looks like a bug in JdbcTemplate autoCommit mode

I'm using Oracle Universal Connection Pool for a DataSource and Spring JdbcTemplate over this one. F.E.: public class JdbcLock { private static final Logger logger = LoggerFactory.getLogger(JdbcLock.class); public static void main(String[]…
Andrey Shindarev
  • 11
  • 1
1
vote
0 answers

SQL Server UPDATE WITH ROWLOCK Doesn't seem to be working

I have a scenario whereby I need to retrieve an INT from a database table, increment it by one then return the old value. I need to ensure that if the stored procedure is called again immediately then the returned value (NextBookingId) is not a…
ledragon
  • 299
  • 6
  • 17
1
vote
1 answer

Innodb Update Lock Order On Primary Key and Secondary Index

When i study on a deadlock caused by two update query. there is some point, I cannot understand. 1. the order of row lock setting? 2. the order of lock setting on Primary and Secondary Index, when update execute? 3. SHOW INNODB STATUS show WAITING x…
BeanMr
  • 11
  • 1
1 2 3
4
5 6 7 8