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
0
votes
0 answers

Horizontal scaling issues with row locking on a permissions system

The requirement I am currently building a permissions system. One of the requirements is that it is horizontally scalable. To achieve this, we have done the following There is a single "compiled resource permission" table that looks something like…
SixteenStudio
  • 1,016
  • 10
  • 24
0
votes
3 answers

Which type of locking mode for INSERT, UPDATE or DELETE operations in Sql Server?

I know that NOLOCK is default for SELECT operations. So, if I even don't write with (NOLOCK) keyword for a select query, the row won't be locked. I couldn't find what happens if with (ROWLOCK) is not specified for UPDATE and DELETE query. Is there a…
Tunahan
  • 303
  • 4
  • 22
0
votes
2 answers

"Atomic section" in Ruby MongoDB driver?

Is there a way to perform a set of non-atomic actions on MongoDB server (using the Ruby driver) as an single atomic action? What I need is basically a lock on certain object/collection.
PJK
  • 2,082
  • 3
  • 17
  • 28
0
votes
1 answer

select for update in stored procedure (concurrently increment a field)

I want to retrieve the value of a field and increment it safely in Informix 12.1 when multiple users are connected. What I want in C terms is lastnumber = counter++; in a concurrent environment. The documentation mentions one way of doing this…
nurettin
  • 11,090
  • 5
  • 65
  • 85
0
votes
1 answer

how to properly merge these 2 query into one update?

This currently work but I would like to change the update statement to include the action of the insert below it, is it posssible? UPDATE cas SET [Locked] = CASE WHEN cas.Locked <> @TargetState AND cas.LastChanged = filter.SourceDateTime THEN…
Fredou
  • 19,848
  • 10
  • 58
  • 113
0
votes
0 answers

Best way to avoid dirty read in SQL server

I found by other posts that in SQL server a read can't be locked by another read. So I do as following BEGIN TRAN -- I used this update to lock specific row UPDATE TBL_BALANCE SET TBL_BALANCE.DUMMYCOLUMN = 1 SET WHERE TBL_BALANCE.ACCOUNT =…
Esty
  • 1,882
  • 3
  • 17
  • 36
0
votes
1 answer

Does MySQL InnoDB locks multiple rows with READ_COMMITTED isolation level?

I am in trouble while resolving LOCK WAIT TIMEOUT EXCEED error with MySQL InnoDB. I have gone through this article and it says if we use isolation level READ_COMMITTED then my update query should lock only those rows which match the WHERE condition,…
Vishal Zanzrukia
  • 4,902
  • 4
  • 38
  • 82
0
votes
0 answers

How to lock related rows so any select on them will wait till the transaction is complete?

I'm building a game session, and each session has many states. When the game asks for the next step, the current game session is selected, and then the latest game state chosen. The user makes an action on the state, returns it, it is checked…
phazei
  • 5,323
  • 5
  • 42
  • 46
0
votes
1 answer

(my)SQL Row Lock while user enters data c#

I'm about to create a application with the use of a database, where multiple users are allowed to edit information about an entity. tldr: How to lock a row in a database while a user is editing it's information in C#. I have done some research on…
CularBytes
  • 9,924
  • 8
  • 76
  • 101
0
votes
1 answer

Row Level Locking - MySQL - for update

I'm still confused about table row locking. I'm using MySQL/PHP and here is my scenario. I have a set of tables that my application uses to keep track of requests and posts. A user creates a posting (table POSTING (P)) for an item (table ITEM (I))…
NEW2WEB
  • 503
  • 2
  • 8
  • 22
0
votes
1 answer

Which records are locked with MySQL's 'SELECT ... FOR UPDATE'

I've read and tested row-level locks in MySQL's InnoDB but I still find it hard to actually say "I know how locks work in MySQL"! Here's my test data: mysql> select * from lockable; +----+----+----+ | id | c1 | c2 | +----+----+----+ | 1 | A | A …
Mehran
  • 15,593
  • 27
  • 122
  • 221
0
votes
0 answers

Oracle 9i database - ORA-00054 RESOURCE BUSY

Looking for some advice. Yesterday, a colleague tried to update a row in an Oracle 9i database via Toad. She killed Toad in Task Manager as it hung as Not Responding. Now, when we try to update the row, we get the ORA-00054: resource busy and…
Louise
  • 107
  • 1
  • 12
0
votes
2 answers

Unix FreeTDS Isolation Level Sybase

According to the Sybase Documentation (http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbusage/udtisol.html) there is one paragraph: [...] The default isolation level is 0, except for [...] and TDS connections,…
Max
  • 33
  • 6
0
votes
1 answer

Data locking prevents query from running - MySQL InnoDB

We need to run a script every 24h that calculates sums & avarages of data from 1 big log table and updates the data in the Main_Table (Innodb) that has many select/update queries all the time (about 1 per second) I ran a test query on a local DB…
SiteAppDev
  • 141
  • 2
  • 8
0
votes
1 answer

oracle: release lock on a single row (while retaining locks on other rows)?

With Oracle is it possible to (with a single database connection): lock a single row (row1) then lock another row (row2) release the lock on row1 (retaining the lock on row2) obtain a lock on another row (row3) release the lock on row2 (retaining…
ErikR
  • 51,541
  • 9
  • 73
  • 124
1 2 3 4 5 6
7
8