0

I have a table with columns: * ID - primary key * DATA - some data I want to select * LOCKED - 0 or 1 (1 if row was read)

I want to create sql mechanism to get every row only one times but many users trying to read from this table at the same time.

Currently, body of my stored produdure looks something like that:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
    PREPARE stmt FROM @findQuery; << select row with LOCKED = 0
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    PREPARE stmt FROM @updateQuery; << set LOCKED = 1
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

COMMIT;

This solution works, but for big load I catch deadlocks exceptions. Is there any better (faster) solution?

I work with Springboot and JDBCTemplate.

ks099
  • 93
  • 1
  • 2
  • 11
  • *"This solution works, but for big load I catch deadlocks exceptions. Is there any better (faster) solution?"* No also trying to roll out your own mechanism to isolate transaction is a bad idea... Unless you willing to use MyISAM as table engine which table locks then you would not get deadlocks on locked records annymore.. – Raymond Nijland Mar 28 '19 at 14:24
  • i also suggest that you post table structures `SHOW CREATE TABLE table` for the tables involved in the question. And some example data and expected results.. See [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) for providing those.. – Raymond Nijland Mar 28 '19 at 14:28
  • This question also tends to be the direction of MariaDB's [System-Versioned Tables](https://mariadb.com/kb/en/library/system-versioned-tables/).. This is how you can [simulate](https://stackoverflow.com/questions/17925435/best-practice-for-a-mysql-data-versioning-system) it on MySQL – Raymond Nijland Mar 28 '19 at 14:33

0 Answers0