Hi,
I investigated further and it looks like the problem is a SQL Server one. It is due to the way SQL Server optimizer dynamically chooses lock granularity.
To understand what happens, I first tried to run the two following transactions in SQL Server Management Studio. Note the two requests make updates on different rows :
Code:
begin tran
update ID_GEN set sequence_next_hi_value=1 where sequence_name='CRENEAU_SEMAINE'
Code:
begin tran
update ID_GEN set sequence_next_hi_value=1 where sequence_name='PERIODE'
In this case I have a deadlock on the second request.
Then I tried similar request on an other table :
Code:
begin tran
update SITE set LIBELLE='test' where ID=1
Code:
begin tran
update SITE set LIBELLE='test' where ID=10
In this case there's no deadlock while the requests are very similar !
So it looks like SQL Server doesn't choose the same lock mode (either row, table or block) in the two cases. Maybe the algorithm takes into account the number of records in the table, the size of each record, etc ...
I could reduce number of deadlocks by creating an index on sequence_name column in ID_GEN table, but it doesn't fully solve the problem since I still have deadlocks sometimes (no more in SQL Server studio anyway, but still from Hibernate). From what I read the more reliable solution would be to add hints in SQL requests (something like "with (readpast)") but I am not sure about the consequences of this. And most of all I can't see any solution to tell Hibernate to add this in requests for id generator !
The only reliable workaround I found is to have one table for each id generator. I mean something like that :
Code:
@Id
@TableGenerator(name = "CreneauJourIdGenerator", table = "CRENEAU_JOUR_ID", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauJourIdGenerator")
@Column(name="ID")
private Long id;
Code:
@Id
@TableGenerator(name = "CreneauSemaineIdGenerator", table = "CRENEAU_SEMAINE_ID", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "CreneauSemaineIdGenerator")
@Column(name="ID")
private Long id;
I don't find it a great solution, but it works a least !
From all this, my conclusion is that if you want to use table generator on SQL Server, and if you have requests with a lot of insert requests (an so a lot of update requests on ids table), I would recommend to have one table for each id generator.
Any comment about this ?
Login
FAQ
Search
