Questions tagged [serialization]
The serialization tag has no usage guidance.
30
questions
2
votes
1
answer
708
views
How two phases locking ensures serializable?
I have read many sources, and they say one of the ways to implement serializable in the database is to use two-phase locking. But I don't really understand how two-phase locking could ensure ...
1
vote
0
answers
26
views
Consistency in serializable concurrent transactions
My DBS textbook says that if each transaction individually maintains consistency in the system, in the case of concurrent transactions, serializability ensures consistency. I am not able to understand ...
- 11
0
votes
1
answer
234
views
SERIAL for multi-column unique constraints
I'm trying to build an append-only data model in Postgres, using the following schema:
CREATE TABLE IF NOT EXISTS account (
id UUID DEFAULT uuid_generate_v4(),
user_id INT NOT NULL,
...
- 1
1
vote
1
answer
868
views
SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates?
Background:
I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization. The constraint is ...
- 253
7
votes
2
answers
473
views
Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?
I have come across the following line in a text book (Database System Concepts
Textbook by Avi Silberschatz, Henry F. Korth, and S. Sudarshan $6e$) page no. 686:
Thomas’ write rule allows schedules ...
- 173
5
votes
1
answer
3k
views
What is an Intent Lock in SQL Server
I have this query
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE c
SET c.Score = 2147483647
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000;
Which have these stats
+----------...
- 427
15
votes
2
answers
5k
views
Does SQL Server's serializable isolation level lock entire table
Me and a colleague of mine discussed the implications of use of the serializable isolation level. He said it locked the entire table, but I disagreed to that telling him it potentially could but it ...
- 427
5
votes
1
answer
974
views
PostgreSQL Serialisation failure on different ids
Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2:
begin ...
- 53
0
votes
0
answers
915
views
PostgreSQL Race condition and Serializable
Hello I try to debug a tricky race condition in my app (golang, pq, sqlx, postgresql Debian 10.6-1.pgdg90+1).
I have a table
CREATE TABLE j (
id BIGSERIAL PRIMARY KEY NOT NULL,
...
- 101
5
votes
1
answer
951
views
What is the meaning of Order preserving Conflict Serializable schedules?
I was going through the concept of order preservation in Conflict Serializable class and I came across Order preserving conflict serializable (OCSR in short). The following is the definition of OCSR ...
- 151
1
vote
0
answers
101
views
View serializable schedule
Any view serializable schedule that is not conflict serializable must contain a blind write.
(Source: Wikipedia)
Why is this statement true? I referred some links like this but am still not able to ...
- 111
4
votes
1
answer
1k
views
Forced serialization on inline table valued function
Apologies if this is a stupid question. We are attempting to convert our multi-statement table valued functions (MSTVFs) to inline table valued functions (ITVFs), to avoid the forced serialization of ...
- 575
2
votes
2
answers
2k
views
Are MySQL InnoDB transactions serializable?
I am confused whether or not MySQL transactions are serializable and whether or not read-anomalies are guarded against?
For example, what if I SELECT a value from a row, then UPDATE that row by ...
- 459
1
vote
1
answer
57
views
Serializability schedules: can someone help me understand it?
I am struggling with the Databases courses I have, so I am trying to find answer to my question.
This has been asked:
Add the appropriate locks to enforce serializability on the following
schedule:
...
- 113
1
vote
0
answers
24
views
Do start and end points change conflict serialisability?
If we have two transactions, and the following schedule:
S1 R1(X) S2 R2(X) R2(Y) E1 W2(X) W2(Y) E2
where Si and Ei indicate the start and end of the ith transaction respectively. Does this change ...
- 177
0
votes
1
answer
1k
views
Storing data in multiple rows vs serialized data
I am currently designing a database table where I store list of courses chosen by students for a session. My current design has to do with storing each course in a row and a student can have up to 30 ...
- 103
0
votes
1
answer
1k
views
Postgres: turn off formating for xml export?
I want to export some table to xml file:
\o output.xml
SELECT table_to_xml('modified_records', TRUE, TRUE, '');
However, I'm struggling with psql formatting. The best I managed to get is:
\t on
\x ...
- 157
3
votes
3
answers
16k
views
What is meant by a blind write in a schedule?
I know that every conflict-serialisable schedule is view-serialisable but the converse is not true. I read in a webpage that when a schedule is view-serialisable but not conflict-serialisable then ...
- 219
2
votes
1
answer
1k
views
PostgreSQL visibility of records changes from other transaction inside the current one
I have a table "tst" which has a "status" column using which we process records in that table.
Once the records are processed we set the status to 'Y';
#\d tst
Table "core.tst"
...
- 363
4
votes
1
answer
878
views
Challenges with Key with Gaps?
I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.
Each time during the error, the primary key ...
7
votes
1
answer
25k
views
SELECT in trigger function in two tables
My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE measurements (
measurement_id SERIAL PRIMARY KEY NOT NULL,
...
8
votes
1
answer
2k
views
debugging PostgreSQL serialization failures
I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format:
...
- 287
2
votes
1
answer
701
views
Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?
On the PostgreSQL Concurrency With MVCC page, it says:
know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation ...
- 555
1
vote
0
answers
301
views
Turn off serialization failure logging
Is it possible to turn off serialization failure logging? If so, how?
For example, my log is filled with this example:
ERROR: could not serialize access due to read/write dependencies among ...
- 555
1
vote
1
answer
1k
views
How to configure read transactions to never cause other write serialization transactions to fail
I am protecting critical transactions with application mutexes and SERIALIZABLE.
I presume that this makes these operations as acid as possible, but now I'm concerned that reads might cause failures ...
user32234
1
vote
3
answers
6k
views
psql: id SERIAL PRIMARY KEY not starting with 1?
This is something I don't understand, can somebody explain?
I create a table
CREATE TABLE cali (id SERIAL PRIMARY KEY,
ALK_from char,
ALK_to char,
AND_from char,
AND_to char);
Then ...
- 107
41
votes
5
answers
35k
views
Locking issue with concurrent DELETE / INSERT in PostgreSQL
This is pretty simple, but I'm baffled by what PG does (v9.0).
We start with a simple table:
CREATE TABLE test (id INT PRIMARY KEY);
and a few rows:
INSERT INTO TEST VALUES (1);
INSERT INTO TEST ...
- 551
5
votes
2
answers
4k
views
How do I implement insert-if-not-found for transactions at serializable isolation level?
I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following.
We have a table called artist with 2 columns, (name, id) where name is the ...
- 208
3
votes
1
answer
3k
views
Send data from SQL to a serial port or IP address
I've been asked to integrate SQL transaction data with a video monitoring system in real-time. The video system's application is only capable of receiving data by listening on a serial port or a ...
- 561
2
votes
1
answer
4k
views
Data type for protobuf objects in mysql
I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp.
+------------+...
- 11.7k