New answers tagged locking
0
You could use transaction level advisory locks to serialize your operations:
BEGIN;
-- take an advisory lock for the duration of the session
SELECT pg_advisory_xact_lock(4711);
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');
COMMIT;
The number 4711 is an arbitrary constant.
Then the second transaction using the same code ...
0
SERIALIZABLE preserves new rows to be added to the result set while REPEATABLE READ preserves actuals rows to do not change or be deleted.
4
Q1
Is it possible that T2 reads,updates and commits the row in question
BEFORE T1 gets to read it ?
A1
Yes, as noted by Paul White here:
The repeatable read isolation level provides a guarantee that data
will not change for the life of the transaction once it has been read
for the first time.
Also due to lock escalation the query can start with row locks ...
0
Row locks are not permanently stored in the shared lock table, but on the row itself, so you cannot simply query for them.
To figure out which rows in a table are locked by concurrent transactions, you could run
SELECT id FROM mytable
WHERE id NOT IN (SELECT id FROM mytable
FOR UPDATE SKIP LOCKED);
0
As documented in the manual pg_locks contains a column database which you can join to the pg_database view:
select l.*
from pg_locks l
join pg_database d on d.oid = l.database
where d.datname = current_database();
1
What is the Postgres's equivalent/closest lock of Microsoft SQL Server's IX (Intent Exclusive) lock?
You need to take a step back and check what Intent Locks are made for.
Transaction locking for MS-SQL server is broadly explained here:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide
And for PostgreSQL it's broadly explained here
https://www.postgresql.org/docs/current/mvcc.html
MS-SQL server ...
0
You can use a series of different monitoring tools and queries that actively watch, log, and report blocking issues. The following would be my recommendation of where to start:
Adam Machanic's sp_WhoIsActive - This will tell you when a query is locked, how long it's been locked for, what is the query blocking it, and other information about why a query is ...
2
we are thinking of moving our updating records operations over from c# to sql
Typically the performance benefits of multi-row DML outweigh any increased blocking. Plus you should be using transactions when performing a multi-row update from C#, in which case you'll have similar locking footprint to a server-side multi-row update: Every row updated will be ...
-1
Select the session id and kill the process which is holding the lock on schema user.
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status,
b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;
and then
SQL> select inst_id,sid,serial# from gv$session where ...
0
If you dont want users to insert data to a table - remove the rights of insert/update/delete on this table.
Locking a table within transaction act differently, it depends on what kind of lock scheme it have etc etc.
Elaborate your scenario in detail.
Meantime check this http://infocenter-archive.sybase.com/help/index.jsp?topic=/com.sybase....
Top 50 recent answers are included
