Questions tagged [serialization]
The serialization tag has no usage guidance.
29
questions
0
votes
1answer
25 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
vote
1answer
132 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 ...
0
votes
0answers
32 views
How to store very very big Array in MySQL (separator / json_encode / serialize)?
I have project with saving timestamps from SCADA system to localhost MySQL-DB. All works fine but thinking about storing datas (read many threads and array storing isnt most efficiency method). Every ...
7
votes
2answers
276 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 ...
4
votes
1answer
976 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
+----------...
11
votes
2answers
2k 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 ...
4
votes
1answer
348 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 ...
0
votes
0answers
408 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,
...
4
votes
1answer
451 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 ...
1
vote
0answers
71 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 ...
4
votes
1answer
723 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 ...
2
votes
2answers
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 ...
1
vote
1answer
46 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:
...
1
vote
0answers
16 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 ...
0
votes
1answer
878 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 ...
0
votes
1answer
714 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 ...
3
votes
3answers
10k 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 ...
1
vote
1answer
612 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"
...
4
votes
1answer
761 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 ...
6
votes
1answer
18k 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,
...
6
votes
1answer
1k 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:
...
2
votes
1answer
435 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 ...
1
vote
0answers
173 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 ...
1
vote
1answer
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 ...
1
vote
3answers
5k 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 ...
38
votes
5answers
28k 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 ...
5
votes
2answers
3k 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 ...
3
votes
1answer
2k 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 ...
2
votes
1answer
3k 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.
+------------+...
